October 24, 2007 at 8:41 am
I recently got a new upgraded PC from my HelpDesk dept which is currently running Win XP 2002 (SP2) . My PC runs MSDE (currently version: 8.00.2039 SP4). After the upgrade, I reinstalled MSDE as I normally do.
On another server (server A) which runs SQL Server Standard 2000 (SP4), I have a linked server (Server Type: "SQL Server" connection) to my PC. Ever since my PC was upgraded, I can not use the linked server to server A to access my PC. I get the following message: Error 17: SQL Server does not exist or access denied".
I have been working on this for the last few days (and ready to pull my hair out :w00t:). I found some posts on Sqlservercentral.com but none of the advice helped me in my situation. W/o creating a huge laundry list of what I tried, here is what I have tried to get the linked server to work:
1) Setup both server A and my PC to use TCP/IP as their default protocols. Setup an alias for my PC on server A under the client network utility.
2) Verified that the username/password that I am using for the linked server connection is fine. I even tried to setup the linked server to connect as "SA" (just for testing purposes) to see if I got a different error message.
I found a great link (http://www.webservertalk.com/archive134-2004-4-174352.html). I even checked the five possibilities that the tech mentions in the posting ... none of them solved my problem. At the end of this post, however, I noticed it was updated to include what the resolution was to fix the problem. It mentions that the following policies in Windows 2003 had to be changed to allow linked servers to connect from another SQL 2000 server:
Network access: Do not allow anonymous enumeration of SAM accounts: Disabled
Network access: Do not allow anonymous enumeration of SAM accounts and shares: Disabled
Network access: Let Everyone permissions apply to anonymous users: Enabled
Network access: Restrict anonymous access to Named Pipes and Shares: Disabled
FINALLY, MY QUESTION IS THIS: HAS ANYONE EXPERIENCED A SIMILIAR PROBLEM WITH LINKED SERVERS CONNECTING TO MSDE on XP? DID YOU HAVE TO CHANGE THE SECURITY POLICIES? - IF SO, WHAT POLICIES DID YOU HAVE TO CHANGE (I am not screaming, I just wanted CAPS for emphasis at the end of a very long post :)).
I am hoping that in this vast community of SQL users that someone has experienced my problem and can please help me.:D Thanks in advance.
Norene Malaney
October 25, 2007 at 10:50 am
Does a regular remote connect works (not as linked server)?
October 25, 2007 at 11:06 am
Thanks for the response.
Here is what I found:
1) I can ping my PC from server A so that means that they can talk to each other on the network (via TCP/IP).
2) I can NOT log into my PC's db from server A using Query Analyzer using the account name/password that I specified in the linked server properties. I get the following message:
Microsoft ODBC SQL Server Driver
Named Pipes
SQL Server does not exist or access denied
3) I can NOT connect to my PC's db server from server A using Enterprise Manager on server A (I get same error message as before).
It looks like named pipes is the culprit. Not sure what to do. I had tried setting both up to use TCP/IP as first protocol in SQL and setup aliases for each other but that didn't work - got the same message.
Norene Malaney
October 25, 2007 at 11:33 am
The ping is a good start.
1) Is there a firewall running?
2) Can you connect to your MSDE using query analyzer on another machine (not using the linked server name). Sql authentication is the easiest to test.
3) TCP/IP must be enabled for the MSDE server using svrnetcn.exe (server network utility) at the MSDE PC. + restart MSDE (just to make sure the new settings are picked up)
If you run the netstat -a command in a dosbox at the MSDE, there should be an
TCP mymsdepcname:ms-sql-s mymsdepcname:0 LISTENING
entry
netstat -an also gives the listening port, which is usually 1433
Once you can connect using query analyzer/enterprise manager, then it should be possible to set up a linked server.
October 25, 2007 at 12:58 pm
Here are the answers to your questions. Thanks so much for your help!
1) There is no firewall between server A and my PC.
2) I tried to connect to my PC using Query Analyzer (using SQL authentication) on another PC [PC B] (which runs the same Operating sys as my PC) - and the user was able to connect. Therefore, it seems like the problem is being caused by:
a) the fact that server A is running SQL server (Note: PC B is not running any versions of SQL Server)
b) the fact that server A is running Windows 2000 SP4 (Note: PC B is running the same operating sys as my PC)
c) something is misconfigured protocol-wise on server A
3) I don't seem to have "svrnetcn.exe" on my PC. 🙁
4) When I run netstat -an. I do not see a listing for "port 1433 listening".
I think I am getting closer to the solution. It is just that it is near the end of the day and I don't have enough brain power to figure out what all of the results of this new testing means. 🙂
Norene Malaney
October 25, 2007 at 1:10 pm
OK - it is late in the day. Although my "find" feature on my PC did not find svrnetcn.exe. I did find it on my C drive. When I run it, it does say that Named Pipes AND TCP/IP are enabled for my PC.
Norene Malaney
October 28, 2007 at 12:17 pm
Can you connect from server A using the ip address only (not by alias)?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply