March 1, 2005 at 3:34 pm
I am really really spent. I have two SQL Standard 2000 Servers and I can't get server A to connect to Server B but A will connect to B. Server A is to be a production server which had no issues before. Now I can't connect to it from anything. It is a W2k3 machine with one instance of SQL on it and I just updated to SP4 Beta (hoping it would resolve the issue). The second server-Server B is running 2000 SP3a MSDE on XP (windows firewall disabled). I have removed Named Pipes from both client/server network utility and left TCP/IP as the only option and have configured both with the same ports 1433. They are both on the same network and can access each other via file sharing etc. The weird thing is that when I try to register Server B in EM from my XP box it is shown as a Server that I can choose from. (Isn't that an active list of ODBC Servers???) When I attempt to register I get "SQL server does not exist or access is denied". I have the correct credentials I have literally rechecked everything dozens of times!!! I am tempted to just reinstall SQL all over again but I'd like to understand why this is happening. I really don't want to reinstall cause this is a multi partioned server with ldf's and mdf's on different raid arrays etc and of course it will take a little time to reinstall (it's looking more and more appealing though). I also have NIC teaming setup - any issues with SQL and that??? Any thoughts or ideas would be greatly appreciated!!!! Thanks.
March 1, 2005 at 4:11 pm
1. Are you getting this error ?
Server: Msg 18452, Level 14, State 1, Line 1
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
2. Instead of using Enterprise Manager to configure linked servers, edit the below SQL and change the ServerB to the true name and then use Query Analyzer connected to "Server A" to run:
sp_addlinkedserver @server = 'ServerB' , @srvproduct = 'SQL Server'
What happens ?
3. On Server B, set up a SQL Server login (not integrated security) , change 'ServerB' 'LOGINID' and 'PASSWORD' as appropriate and then use Query Analyzer connected to "Server A" to run:
SELECT a.*
FROM OPENROWSET('SQLOLEDB','ServerB';'LOGINID';'PASSWORD',
'SELECT * FROM sysdatabases') AS a
What happens ?
SQL = Scarcely Qualifies as a Language
March 2, 2005 at 7:01 am
Thanks for responding Carl!!
1- I am not getting that error. The error I get when I attempt to register the server (not set up a linked server) is "SQL Server does not exist or access is denied,Connection Open (Connect())"
2- I did this both ways and CAN connect to Server A (W2k3-the problem server) from Server B (XP) as a linked server.
3-This works also. I got a list of of DB's on that server.
But I am still having issues Registering Server A in my Server Group-this is where it fails. I am not really familiar with linked servers and from what I see it doesn't have the same features (in the GUI) as Registering an SQL Server (like setting up logins etc.) I figure everything can be done from T-SQL but I am not very good at it yet so the GUI is my crutch!
So I can connect to my server successfully but not register it, whats the difference in connection protocol that would be not allow this Registration to be successful???
Thanks again Carl!
March 2, 2005 at 11:24 am
<so the GUI is my crutch! >
Yes, it gets quite complicated using T-SQL but being unable to reproduce your actions and being unable to produce an audit trail because you used EM will cause the auditor to raise an issue due to Sarabannes-Oxley (for publicly traded companies) and HIPAA (for the Health Care Industry).
I worked for many years with Sybase (the original source of MS SQL Server) and had no choice but to use T-SQL. There were no GUIs for DBAs in the early 90s.
Regarding being unable to register a server with Enterprise Manager, suggest you turn off the Wizard and type in the server name rather than select from a drop down.
If you are still having connectivity problems, see "How to troubleshoot connectivity issues in SQL Server 2000" at http://support.microsoft.com/default.aspx?scid=827422
This also is caused by the target server being too busy to accept connections.
SQL = Scarcely Qualifies as a Language
March 3, 2005 at 11:04 am
Don't know if you have resolved this. Do you have applications connecting to databases on the "problem" server? Can they connect to their databases without any problems?
You might want to download Microsoft's port query tool from http://support.microsoft.com/kb/832919. It will tell you whether port 1433 is open and whether SQL Server is listening on it.
HTH
Don
March 3, 2005 at 11:52 am
I have the problems semi corrected. I can connect to the server now via my XP box internally (TCP/IP or NamedPipes) but not externally. I have both boxes on the same network and subnet but they both have different firewalls as default gateways. I purchased a better firewall and a new server but I need remote access to this new server and I can't seem to get it to work.
I have a third server (which is my production server) that I am trying to connect to this New Server (so I can backup, transfer DB's etc). I have a weird issue going on though:
XP Box --->New Server via TCP/IP (DSN) Computer Name for the Server Works great
Production Server-->New Server via TCP/IP (DSN) Computer Name for server doesn't work but does work if I specify the IP for the Server. I have to enable NamedPipes on the New Server for it to work. I don't know why. Is it possible for different version of libraries to not allow connection. Both Boxes are W2k3 (I don't think SQL 2000 supports that OS though). But XP isn't having any issues.
With the Firewall issue when I route my XP box to the same firewall that the New Server is on I can connect to the XP box from externally but I can't connect to the New Server externally. I have specfied TCP/IP, Static Port in a DSN and it won't work but for the XP box.
Seems to me there is an issue with the TCP/IP connection of this New Server.
I'll take a look at that port scanning tool from MS but I do have ActivePorts that I use and it shows 1433 and 1434 Listening.
Any ideas are greatly appreciated, thanks for taking your time to respond!
March 3, 2005 at 12:52 pm
2003 machines need a config change to participate in distributed queries. look at my explination here.
http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=165231
HTH
Cody Pack
MCSE Windows 2000
MCDBA SQL Server 2000
March 3, 2005 at 2:29 pm
Thanks for resonding Cody. I checked that out in Component Services on my New W2k3 Server and it already had NT Authority\networkservice as the login.
March 3, 2005 at 2:32 pm
Did you allow network access to the service as well?
Cody Pack
MCSE Windows 2000
MCDBA SQL Server 2000
March 3, 2005 at 3:13 pm
Just did. I enabled Network Transactions,Network Adminstration, and Network Clients.
I can establish a connection to this Server with the XP box no problem. The other W2k3 box only connects to it when I specify the IP address (when using TCP/IP) otherwise I have to enable NamedPipes. I'm lost.
Thanks again!
March 3, 2005 at 3:17 pm
i would lean toward DNS issues then
ping servername -a
it should say "Pinging servername.domainname.com......."
if DNS resoultion is working correctly.
Cody Pack
MCSE Windows 2000
MCDBA SQL Server 2000
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply