MS SQL & Msg 11, Level 16, State 1

Randomly, when trying to authenticate to a SQL server, whether via a
mmc connection, or via a SQL Query Analyzer, several DBAs / analysts
get the following error:
"Unable to connect to server <servername>:
Server: Msg 11, Level 16, State 1
[Microsoft][ODBC SQL Server Driver][DBNETLIB]General network error.
Check your network documentation."
This happens from variety of clients, whether XP or 2000. All of the
MS SQL servers are at 2000 SP3a. Everything is on the same LAN, no
firewalls in between.
This error always happens when integrated Windows authentication is
used; however, when using the SA account, no error occurs and the
account logs in.
Communication is established to the servers (green arrow is lit in the
mmc); however, when attempting authentication, that error randomly
occurs. The problem is, that the error will occur here and there, and
then the same server that had an issue with the integrated Windows
authentication with connect just fine via integrated Windows
authentication.
Where should I look? What could be a possible cause? Help.
General Network Errors are one of the most difficult to troubleshoot, due
to the randomness of the problem (and because this type of error can occur
for many different reasons) .
Below are some suggestions to help you diagnose the problem:
Problem Definition
=============================
(1) Has this worked in the past? If so, identify any changes that may have
occurred right before the problem started happening.
Did you apply any security patches or other upgrades?
Has the load changed on the web site or app that is receiving the errors?
Have there been any network configuration changes?
(2) Send us the exact error message with as much error information you can
get from it - with native error code if possible.
This will usually be a number like 10054 or 10061. This information is not
available from all data access technologies, but you should send us as much
as you can get. We need to get the exact error message, either from a log
file, screen shot, or any method that captures the specific error.
Collect :
(1) Exact error message and any error codes returned
(2) Time of day it occurred (*very* important). Does this seem to happen
only during certain times in the day?
(3) Is the problem consistent?
(i) No
"Does it reproduce repeatedly after it happens once?
"How often does it occur? (every 2 hours, once a week, etc)
"Have you noticed any patterns to what is being done on the server when
the problem occurs? For example, certain stored procedures, statements, etc.
"Do only certain clients experience the problem?
"Does it happen at certain times or the day, or under stress?
"Do multiple users experience the problem at the same time?
(ii) Yes
"Can you reproduce from another client like ODBC Test or Query Analyzer?
(4) How do you recover from the problem?
(a)If you are rebooting the entire machine where the error occurred, have
you tried recycling only the process that had the problem?
(b)Do you try to reconnect in code, and if so, does it work right away?
(5) What client are you using? i.e. ADO.NET (SqlClient), ADO, OLE DB, ODBC
(6)Are you using NIC teaming? Teaming means that all of the NICs are bound
together and appear as one IP/physical address.
The following is an example of a section of ipconfig output that indicates
that NIC teaming is being used:
Ethernet adapter public:
Connection-specific DNS Suffix . : [DNS server name]
Description . . . . . . . . . . . : Compaq Network Teaming Virtual Miniport
Physical Address. . . . . . . . . : [NIC address]
DHCP Enabled. . . . . . . . . . . : No
IP Address. . . . . . . . . . . . : [IP address 1]
Subnet Mask . . . . . . . . . . . : 255.255.254.0
IP Address. . . . . . . . . . . . : [IP address 2]
Subnet Mask . . . . . . . . . . . : 255.255.254.0
IP Address. . . . . . . . . . . . : [IP address 3]
Subnet Mask . . . . . . . . . . . : 255.255.254.0
Default Gateway . . . . . . . . . : [gateway]
DNS Servers . . . . . . . . . . . : [DNS 1]
[DNS 2]
Primary WINS Server . . . . . . . : [WINS IP]
Secondary WINS Server . . . . . . : [WINS IP]
Note that the Description of the adapter includes the word "teaming", and
that there are multiple IP addresses all associated with this one adapter.
(7)If there are firewalls, do you have any timeout settings configured?
Connectivity problems can occur if a firewall times out a connection before
the client and SQL are done with it.
(8)For testing purposes, if possible:
i.Disable NIC teaming
ii.Eliminate routers or firewalls if possible to simplify the path between
the client and server. Move the server and client into the same subnet if
possible.
(9) Is there very heavy load on the SQL server when this happens?
Other troubleshotting steps
========================
What MDAC version are you on? if possible try upgrading to the latest MDAC
version
does this happen just from one client machine, some clients, or all client
machines? (you can register sql remotely)
is the issue tool specific? When trusted connections fail from enterprise
manager,do they also fail from query analyzer?
who are you looged on as when the trusted connections fail? Do trusted
connections fail - for all accounts - or just one specific account?
A network monitor trace will assist in determining what is going on in the
network level during this time. Run a network monitor trace from the CLIENT
and SERVER machien simultaneously and have your network admins analyze
these traces (to check for any network problems such as a bad NIC, bad
router,etc).
Please view these additional references as well (available at
http://support.microsoft.com):
TechNet Support WebCast: Known connectivity issues related to "General -
ID: 875285
You may not be able to connect to an instance of SQL Server 2000 Service -
ID: 884012
How to troubleshoot connectivity issues in SQL Server 2000 - ID: 827422

MS SQL & Msg 11, Level 16, State 1

Randomly, when trying to authenticate to a SQL server, whether via a
mmc connection, or via a SQL Query Analyzer, several DBAs / analysts
get the following error:
"Unable to connect to server <servername>:
Server: Msg 11, Level 16, State 1
[Microsoft][ODBC SQL Server Driver][DBNETLIB]General network err
or.
Check your network documentation."
This happens from variety of clients, whether XP or 2000. All of the
MS SQL servers are at 2000 SP3a. Everything is on the same LAN, no
firewalls in between.
This error always happens when integrated Windows authentication is
used; however, when using the SA account, no error occurs and the
account logs in.
Communication is established to the servers (green arrow is lit in the
mmc); however, when attempting authentication, that error randomly
occurs. The problem is, that the error will occur here and there, and
then the same server that had an issue with the integrated Windows
authentication with connect just fine via integrated Windows
authentication.
Where should I look? What could be a possible cause? Help.General Network Errors are one of the most difficult to troubleshoot, due
to the randomness of the problem (and because this type of error can occur
for many different reasons) .
Below are some suggestions to help you diagnose the problem:
Problem Definition
=============================
(1) Has this worked in the past? If so, identify any changes that may have
occurred right before the problem started happening.
Did you apply any security patches or other upgrades?
Has the load changed on the web site or app that is receiving the errors?
Have there been any network configuration changes?
(2) Send us the exact error message with as much error information you can
get from it - with native error code if possible.
This will usually be a number like 10054 or 10061. This information is not
available from all data access technologies, but you should send us as much
as you can get. We need to get the exact error message, either from a log
file, screen shot, or any method that captures the specific error.
Collect :
(1) Exact error message and any error codes returned
(2) Time of day it occurred (*very* important). Does this seem to happen
only during certain times in the day?
(3) Is the problem consistent?
(i) No
" Does it reproduce repeatedly after it happens once?
" How often does it occur? (every 2 hours, once a week, etc)
" Have you noticed any patterns to what is being done on the server when
the problem occurs? For example, certain stored procedures, statements, etc.
" Do only certain clients experience the problem?
" Does it happen at certain times or the day, or under stress?
" Do multiple users experience the problem at the same time?
(ii) Yes
" Can you reproduce from another client like ODBC Test or Query Analyzer?
(4) How do you recover from the problem?
(a) If you are rebooting the entire machine where the error occurred, have
you tried recycling only the process that had the problem?
(b) Do you try to reconnect in code, and if so, does it work right away?
(5) What client are you using? i.e. ADO.NET (SqlClient), ADO, OLE DB, ODBC
(6) Are you using NIC teaming? Teaming means that all of the NICs are bound
together and appear as one IP/physical address.
The following is an example of a section of ipconfig output that indicates
that NIC teaming is being used:
Ethernet adapter public:
Connection-specific DNS Suffix . : [DNS server name]
Description . . . . . . . . . . . : Compaq Network Teaming Virtual Miniport
Physical Address. . . . . . . . . : [NIC address]
DHCP Enabled. . . . . . . . . . . : No
IP Address. . . . . . . . . . . . : [IP address 1]
Subnet Mask . . . . . . . . . . . : 255.255.254.0
IP Address. . . . . . . . . . . . : [IP address 2]
Subnet Mask . . . . . . . . . . . : 255.255.254.0
IP Address. . . . . . . . . . . . : [IP address 3]
Subnet Mask . . . . . . . . . . . : 255.255.254.0
Default Gateway . . . . . . . . . : [gateway]
DNS Servers . . . . . . . . . . . : [DNS 1]
[DNS 2]
Primary WINS Server . . . . . . . : [WINS IP]
Secondary WINS Server . . . . . . : [WINS IP]
Note that the Description of the adapter includes the word "teaming", and
that there are multiple IP addresses all associated with this one adapter.
(7) If there are firewalls, do you have any timeout settings configured?
Connectivity problems can occur if a firewall times out a connection before
the client and SQL are done with it.
(8) For testing purposes, if possible:
i. Disable NIC teaming
ii. Eliminate routers or firewalls if possible to simplify the path between
the client and server. Move the server and client into the same subnet if
possible.
(9) Is there very heavy load on the SQL server when this happens?
Other troubleshotting steps
========================
What MDAC version are you on? if possible try upgrading to the latest MDAC
version
does this happen just from one client machine, some clients, or all client
machines? (you can register sql remotely)
is the issue tool specific? When trusted connections fail from enterprise
manager,do they also fail from query analyzer?
who are you looged on as when the trusted connections fail? Do trusted
connections fail - for all accounts - or just one specific account?
A network monitor trace will assist in determining what is going on in the
network level during this time. Run a network monitor trace from the CLIENT
and SERVER machien simultaneously and have your network admins analyze
these traces (to check for any network problems such as a bad NIC, bad
router,etc).
Please view these additional references as well (available at
http://support.microsoft.com):
TechNet Support WebCast: Known connectivity issues related to "General -
ID: 875285
You may not be able to connect to an instance of SQL Server 2000 Service -
ID: 884012
How to troubleshoot connectivity issues in SQL Server 2000 - ID: 827422

MS SQL - Query Analyzer by IP range

I need to run reports from our database that stores IP addresses when a software license is used. So, I need to know how to run SQL queries to retrieve certain IP addresses from specified ranges. For example:

192.168.1.0 - 192.168.2.255

192.168.3.11 - 192.168.7.128

10.10.10.8 - 10.10.12.255

What commands would help me accomplish pulling all IP addresses in a range. I know I would have to run multiple reports for each range. I tried using the "between" function and "less than/greater than" functions, but I received mixed results. For example:

SELECT * FROM testtable WHERE ip LIKE '192.168.[1-2].[0-255]'

I also tried -

SELECT * FROM testtable WHERE ip>'192.168.1.0' and ip<'192.168.2.255'

Any help would be greatly appreciated! Thanks in advance!Parse your IP addresses into four separate columns. That would make it easy to search or filter by ranges.|||blindman, thank you for the feedback... however, the original DB configured the IP address as it's own field... at this point, there is over 10million or so records, so modifying the DB would be time consuming... any other thoughts would be greatly appreciated...|||Create a function called IP2DWord or so. Its pretty straigtforward. First octet * 256^3 + second octet * 256^2 + third octet*256 + fourth octet. If you use this function on both starting and ending address you can easily select ranges.|||Define: "time consuming"? ;)
There have been a number of questions about IP addresses in a range - I suggest you search the forum www.dbforums.com/search.php and there were some very good suggestions around and about (see above ;)).|||roac, i looked into your suggestion and it seems to be the way to go... thnx all for the feedback...|||If you parse the IP address on the fly using a function, then you can toss any indexing out the window. On 10,000,000+ records don't expect a great response time.
You asked for advice, and I gave it. You are FAR better off parsing the IP Address into four separate columns in your table. You can index them, and you only need to parse each address once. If you absolutely have to have the IP Address as a single column in your table, then keep it and create four additional columns in the table that you populate automatically using insert/update triggers.
You can do this right, or you can do this wrong. You can take shortcuts now, or you can save programming, debugging, and execution time over the life of the application.
'Nuff said.|||For reporting purposes it is required for the IP address to be in 1 column. I will look into separating each IP segment into it's on column, as I agree there's more flexibility when running reports specifically for IP ranges.|||For reporting purposes it is required for the IP address to be in 1 column.Reporting purposes do not require the data to be in one column. A view, sproc, or SQL statement can easily combine the components for reporting.