HELP: SQL Server 2K client do not connect to DB server with IP addresses addeded other the first one

Question

HELP: SQL Server 2K client do not connect to DB server with IP addresses addeded other the first one

fasttrack

SSCertifiable

Points: 6071
More actions
July 31, 2006 at 5:05 am

#75025

Hi
I am setting up a lab on a Windows server 2003 Standard Edition that works as a Database Server. Some clients connct to this database server; clients are on different subnet.
Routing is ruled with Windows server 2003 RRAS.
The differents subnets are created adding IP addresses to the network interface (I mean:
TCP/IP Properties - General - Advanced - IP Settings - IP Addresses - Add)
Clients ping without problem with server, I access to folders.
Then I connected the first client SQL using SQL Server Client, I run registration, Everything works. I can see SQLServer, I managed everything as usual. In this case IP client is 192.168.1.50 and IP for server is 192.168.1.1 (his IP is in the "general" page of TCP/IP Properties (TCP/IP Properties - General).
Than I tried to connect others clients (eg: 192.168.2.50 ,192.168.3.50 ,192.168.4.50 ...), but registration fails, messaging me with the typical message: database does not exist or bad password.
Username and password are correct, besides clients connect to server with ping.
The IP Addresses in the servers are added: 192.168.2.1 ,192.168.3.1 ,192.168.4. ... in TCP/IP Properties - General - Advanced - IP Settings - IP Addresses - Add.
I tried this test:
In one of the client I changed the IP addresses with: 192.168.1.51 (connecting with IP of server with IP in "general" tab),
I tried registration and client connected without problem !!!!!!!!!!!!!!!!!!!!!!!!!!!!!.
So the problem is that connecting with IP that are added other the first IP address it fails. (what setting and must be done?)
Do you know how to solve the problem ?
Thank for your useful help

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 1

I suggest a read of a good tcp/ip book. I'm not too hot on tcp/ip but I'm pretty sure this is to do with subnet masks and you have to set up access to allow this , did some of this a few years ago but can't remember the details - might be routing.

Sure someone else will have the exact solution.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

fasttrack SSCertifiable Points: 6071 More actions · Answer 2

Routing looks OK, I can access clients without problem.

The problem is SQL server, it looks reading only an IP address for NIC instead of all list.

thank

fasttrack SSCertifiable Points: 6071 More actions · Answer 3

Anyone knows some good networking Windows server 2003 forum site.

Thank

David A. Long SSCrazy Eights Points: 8421 More actions · Answer 4

Good luck on finding the site that will cure this issue, the real problem is that the RRAS connection does not "login" or net start workstation, instead it simply authenticates the connection, so the workstation information is not generally available.

SQL Server is not alone in this, that same problem occurs in IIS, and other systems that rely on a user connection that has "logged into" the network (domain or peer to peer).

You can cure this by linking a "login script" to the RRAS authentication, but this is totally a roll your own situation, there are no hand holders at this level.

Look at the examples on the microsoft sites (KB and MSDN) for quarantine extension to RRAS, if you are using Windows 2003 servers.

Andy

fasttrack SSCertifiable Points: 6071 More actions · Answer 5

After lots of tests and drilling down the problem, It looks like that authentication fails on clients when they access with ip addresses that are not in the first IP address on the NIC, but they are added (second, thrid....and so on).

So I agree with you there is something to add, check in router or policy...but what....

At the moment I do not use firewalls, only DB servers and clients.

I have gone around internet, Microsoft site...but I didn't find anything.

Any help will be very appreciated

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 6

As I say I remember doing a similar sort of thing when placing a number of servers within seperate dmz's, so they all had seperate subnets and the issue of using nat to allow them to talk to each other and then using nat twice to get through the firewall and isa server.

It makes sense about the third octet in the ip address - much the same happens in a home network and you normallly have to, in my case, allow the connection through the firewall(s). You need to find a good network/sysadmin person ( which is what I did !! )

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/