October 8, 2003 at 9:29 am
To insulate our users from changes to physical server names, we have been setting up DNS ‘aliases’ for them to point to. IE: if server SRV001 has tcp\ip address 111.111.111.111, then DEV_AP1 (development app 1) would point to 111.111.111.111. The users would then set up a TCP\IP ODBC connection pointing to DEV_AP1. This has been working really well, and we can move apps from db server to db server without the client noticing (for server replacements etc…we have a lot of little apps).
We are now setting up ACTIVE\ACTIVE clustered boxes with named instances. I was very surprised when I discovered that while I could easily connect to SRV001\NM1, I could not connect to DEV_AP1\NM1 (NM1 being the named instance).
- SQL Server 2000, Windows 2000, SQL 2000 client
- Pinging SRV001 and DEV_AP1 returns the same address
- I can easily connect to the default instance of SRV001 and DEV_AP1 using query analyzer or ODBC
- I can connect to SRV001\NM1 using query analyzer or ODBC
- I can set up a SQL Server Alias on each client that points to the correct port of the named instance (ie Server DEV_AP1, port 1185), but I would rather not go there...
- When I try to connect to DEV_AP1\NM1 using query analyzer or ODBC, I get the following error:
SQL Query Analyzer
Unable to connect to server DEV_AP1\NM1:
Server Msg 17, Level 16, State 1
[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied
Does anyone know why? Any suggestions?
Lynda Rab
www.sqlpass.org
October 8, 2003 at 10:26 am
First thing to check, which port is DEV_AP1 listening on? (The first instance is usually 1433, the second instance is usually something else).
Second, is your ODBC connection to DEV_AP1 using the correct port?
-SQLBill
October 8, 2003 at 10:31 am
Okay, I missed your comment about the port issue.
Next, what type of authentication is DEV_AP1 set up to use? (IN Enterprise Manager, right click on the server/instance and check the security tab).
What type of authentication is the user set up to use? (drill down to Security in Enterprise Manager, click on logins, find the login you are using and see what the authentication is set to be).
Lastly, which type of authentication are you using for the ODBC connection?
-SQLBill
October 8, 2003 at 11:00 am
Thx for the quick reply!
DEV_AP1 (SRV001) is set to use SQL Server and Windows authentication. I get the same error if I try to log on using NT authentication that I do using a SQL login (sa). NOTE: I can log on without a problem using NT auth or sa when I try to connect using SRV001 instead of DEV_AP1.
Lynda Rab
www.sqlpass.org
October 8, 2003 at 11:21 am
I need some clarification about your servers setup.
1. Do you have two separted clustered servers installation? One installed with SRV001 default instance and SRV001\NM1 named instance, The otehr has DEV_AP1 default instance and DEV_AP1\NM1 named instance installed.
2. You are try to setup DNS 'alias' from named instance SRV001\NM1 to DEV_AP\NM1.
3. You are able to connect to named instance SRV001\NM1 but unable to connect to DEV_AP1\NM1.
October 8, 2003 at 12:21 pm
1. Do you have two separted clustered servers installation? One installed with SRV001 default instance and SRV001\NM1 named instance, The otehr has DEV_AP1 default instance and DEV_AP1\NM1 named instance installed.
NO.... DEV_AP1\NM1 = SRV001\NM1
DEV_AP1 is just a DNS alias for SRV001... when you ping DEV_AP1 and SRV001... you get the same address
2. You are try to setup DNS 'alias' from named instance SRV001\NM1 to DEV_AP\NM1.
Yes? We just want the clients to point to the logical DNS alias DEV_AP\NM1 instead of SRV001\NM1 so that they are insullated from any server name changes. For example: Database DBACCT01 is physically located on SRV001\NM1 (NOTE: in DNS DEV_AP1 is mapped to the same IP address as SRV001). Then due to resource problems... we want to move DBACCT01 to another clustered server with more capacity. We then create a new named instance on SRV002 -> SRV002\NM1 and point the logical DEV_AP1 to SRV002. This means we can move databases from server to server without impacting any clients. (And this works perfectly fine as long as you don`t use named instances!)
3. You are able to connect to named instance SRV001\NM1 but unable to connect to DEV_AP1\NM1.
Yes!
]
Lynda Rab
www.sqlpass.org
October 8, 2003 at 12:29 pm
SRV001\NM1 is virtual SQL Server name with its virtual ip address, In your DNS alias setup, you may have to configure DEV_AP1\NM1 to point the origial virtual ip address of SRV001\NM1. It is just a guess as haven't done DNS alias setup.
October 9, 2003 at 6:20 am
Thanks for the input.... but since I want DEV_AP1 to refer only to the TCP\IP part of the address... I can't set DEV_AP1\NM1 up that way in DNS.
I am beginning to think this is just a limitation of the SQL 2000 client... and can't be done. (Similar to the fact that a SQL Server 7 client can't connect to a named instance without the port being specified.)
SRV001 (physical server name)and DEV_AP1 (DNS alias pointing to the same IP address as SRV001) are the TCP\IP part of the address.
NM1 is the port part of the address
The SQL Server 2000 client is smart enough to resolve that that SRV001 goes to 111.111.111.111 port 1433 (default instance) and that SRV001\NM1 resolves to 111.111.111.111 port 1185.
And while it is smart enough to resolve that DEV_AP1 resolves to 111.111.111.111 port 1433 (default instance). It is not smart enough to resolve that DEV_AP1\NM1 should resolve to 111.111.111.111 port 1185.
I guess I can't do it! Thx anyway for your input.
thx... lynda
Lynda Rab
www.sqlpass.org
October 9, 2003 at 7:36 am
When you installed default instance SRV001, IT was assigned a virtual ip address, 111.111.111.111. When installing named instance, you can create SQL Server virtual server name as SRV002\NM1 and assigned ip address 222.222.222.222. If you ping SRV002, you will get 222.222.222.222. Then setup DNS alias from SRV002 to DEV_AP1. In this way, It should work.
October 9, 2003 at 7:37 am
I haven't had a problem connecting to a named instance on SQL Server 2000 when using an ODBC DNS. We do have an app that uses a BDE connection and we had problems with that. Somehow the DBAs got it working. Don't totally recall how but think it had something to do with port blocking on some of the routers/firewalls.
Anyway, I found a work around that solved the problem and am still using it. If you use the SQL Server Client Network Utility (cliconfg.exe) you can create an alias where you specify the server (cName or whatever) and also specify a port. You then have an alias that can be referenced in the ODBC without \Named Instance. Good luck
October 9, 2003 at 8:46 am
Chances are good that somewhere along the way, port 1434 was shut of on the server to remediate the SQL Slammer virus. When the port is shut off, the client can not resolve the named instance.
You can probably connect to SRV001\NM1 because the last successfull connection was cached in the registry at HKEY_LOC_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SuperSocketNetLib\LastConnect.
This will remain cached until the first unsuccessful connection.
My GUESS is that if you look in that registry key, you will see an entry for SRV001\NM1 but NOT DEV_AP1\NM1.
If this is the case, then the only option is to set up client side aliases that point to the correct server\port combination.
October 9, 2003 at 3:08 pm
--When you installed default instance SRV001, IT was assigned a virtual ip address,
--111.111.111.111. When installing named instance, you can create SQL Server -----
--virtual server name as SRV002\NM1 and assigned ip address 222.222.222.222. If
--you ping SRV002, you will get 222.222.222.222. Then setup DNS alias from
--SRV002 to DEV_AP1. In this way, It should work.
Though I did mention clustering is the way we want to go… the machine I am talking about is not clustered. Just a server that has a default instance, and 1 named instance (no cluster… no virtual server name). Therefore 1 IP address….. physical SRV001 IP address: 111.111.111.111 DNS alias DEVAP1: 111.111.111.111.
--I haven't had a problem connecting to a named instance on SQL Server 2000 when
--using an ODBC DNS. We do have an app that uses a BDE connection and we had
--problems with that. Somehow the DBAs got it working. Don't totally recall how but
--think it had something to do with port blocking on some of the routers/firewalls.
--Anyway, I found a work around that solved the problem and am still using it. If you use the SQL
--Server Client Network Utility (cliconfg.exe) you can create an alias where you specify the server
--(cName or whatever) and also specify a port. You then have an alias that can be referenced in
---the ODBC without \Named Instance. Good luck
Thx…. I does work if I set up an alias on each client that points to server DEV_AP1 and port 1185 .. and that is what we may have to do…. But that would mean we has to go to each individual client and set up the aliases…which we don’t want to do J
--Chances are good that somewhere along the way, port 1434 was shut of on the
--server to remediate the SQL Slammer virus. When the port is shut off, the client
--can not resolve the named instance.
--You can probably connect to SRV001\NM1 because the last successfull connection was cached
--in the registry at
HKEY_LOC_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SuperSocketNetLib\LastConnect.
--This will remain cached until the first unsuccessful connection.
--My GUESS is that if you look in that registry key, you will see an entry for SRV001\NM1 but NOT
--DEV_AP1\NM1.
--If this is the case, then the only option is to set up client side aliases that point to the correct server\port
--combination
Interesting info that will be useful in figuring this out… Since this is a server within our firewall, port 1434 has not been shut off. I looked in the registry key and found the following:
SRV001\NM1 -123456789:tcp:SRV001,1185
DEV_AP1 -2222222222:tcp:DEV_AP1,1433
SRV001 -2222222222:tcp:SRV001,1433
I deleted the entries and then reconnected to each one… just to make sure the entries were not cached.
We may have to use client side aliases if I can’t resolve the issue.
NOTE: I was talking to one of our network guys… and he said I should not call DEV_AP1 a DNS alias. It is a DNS Host (A) type record entry pointing to the same IP address as SRV001. He is looking into whether I should be defining DEV_AP1 as a DNS Alias (CNAME) record. Sorry for the confusion!
Lynda Rab
www.sqlpass.org
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply