SQL Express remote connect problem 1

[Glasgow]

I have two SQL 2005 EXPRESS instances installed. One on my PC and one on our domain server.

When I login to my PC I can connect to my local SQL instance from Management Studio.

Using remote desktop and logging in with same domain user/password as on my PC, I can open Management Studio on the server and connect into the server SQL instance no problem.

However I cannot connect to the server instance from Management studio on my PC nor can I connect to my PC instance from Management Studio on the server.

I get the same message that hints that this could be due to the fact that the default SQL install does not allow remote users to connect. However, both SQL instances are configured to allow remote connection and to support Windows Authentication Mode for login.

Any help would be greatly appreciated as this is driving me mad!

Thanks in advance.

 

[Glasgow]

Just for the hell of it I tried messing with different TCP/IP & named pipes combinations and I noticed 3 things in the process:

a) Even if I mis-type the instance name I get the same error. How useful! Which leads me to believe that maybe it's just some kind of timeout issue - it may not even be getting as far as detecting the instance.
b) Under some circumstances, the message changed to 'cannot generate SSPI context'
c) I did actually successfully gain access to the server instance from my PC (but not vice versa) when I changed remote connection to use 'named pipes only'.

I'm not sure I'm any the wiser. I did also stumble across:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277

but it didn't get me anywhere as the SQLBrowser service is running and windows firewall disabled.

 

[Glasgow]

Still deadlock but I can reliably connect from PC to server if 'named pipes only' setting is used for remote connections to the server.

I can connect from the server to an instance of SQL 2000 but not 2005 Express.

 

[gmmastros]

Start here: faq962-5481

Also... If you are running vista, be aware that the firewall now blocks outgoing ports too. I think this is 'off' be default, but you should still check it.

If you still have problems, let us know.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Glasgow]

Thanks for the reply. Re FAQ steps:

1. ping - yes
2. telnet - log contains line "Server is listening on [ 'any' <ipv4> 2390]". telnet to this port generates blank screen with cursor (does not flash)
3. File share - yes
4. The failure to connect can be emulated from other workstations.
5. Firewall on problem (host) machine is disabled

Interestingly, sqlcmd -L will list the sql server as one of the available ones on the host PC but not on the remote machine from which I'm trying to connect. Any further thoughts?

It very much seems like a problem where it just "can't see" the sql instance.

 

[gmmastros]

It appears as though your server is using tcp/ip port 2390.

So... Start up Management Studio. For server name put the IP Address of the server, then a comma, and the port number. Like this...

Server name: 192.168.1.17,2390

If this works, then I would suspect a DNS issue on your network.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Glasgow]

I'm not in the office today (dammit) nor, perhaps, Monday. I will try ASAP. Yes, in the context of my very limited knowledge of DNS, this does seem to make a certain amount of sense.

Thanks again. Coincidentally, you just got a star from me yesterday for

http://www.tek-tips.com/viewthread.cfm?qid=1080798

which I'll probably use to retrieve SQL password from user on start up for SQL authenticated login. Thanks.

 

[Glasgow]

Also, as a side issue, I'm not consistently seeing a 'listening on' message within the error logs - e.g. on other servers. Is there any other way of determining the correct port?

 

[gmmastros]

On the server...

Click Start -> Programs -> Microsoft SQL Server 2005 -> Configuration Tools -> SQL Server Configuration Manager

On the left, expand 'SQL Server 2005 Network Configuration'
Click on 'Protocols for (Your instance name)'
On the right, make sure TCP/IP is enabled. (Right click on it if it isn't.)
Double click TCP/IP
Click 'IP Addresses' tab
In the section for 'IPAll', you will see the TCP Port.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Glasgow]

Thanks George, that's useful to know.

I'm trying something similar with two machines in a peer-to-peer network (1 x XP Home & 1 x Win 2k) both with their own SQL 2005 instances and getting very similar symptoms to what I was getting in the office. The Win 2k instance can connect to the XP instance but not vice versa.

Interstingly, the XP instance triggered a Norton pop-up to permit access from the the Win2k box and only once I granted permit access could I get in. No such pop-up was triggered when attempting in reverse although both are running Anitivirus 2006. I tried disabling worm protection under Norton on the Win2k box but that didn't help me get access from the XP box. The problem machine at the office is also running Norton.

Do you think this could be a factor?

It's an exhausting process!

 

[gmmastros]

Yes

You may want to disable Norton (temporarily) to see if you can connect. If you can, then you've successfully identified the problem. I suspect a quick call to Symantec would resolve the issue.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Glasgow]

Hmmm. Doesn't seem to have had any effect in the peer-to-peer environment. Will experiment when I get back to the office and report.

In the meantime have a good weekend and thanks again.

 

[Glasgow]

Well the good news is that, in the office environment, I can indeed connect using sql server IP address and port number and I did not have to tinker with Norton. So I think a star has been well and truly earned. Thank you for your persistence.

Could it be a problem with WINS rather than DNS? My knowledge of these services could be scrawled on a pinhead but I believe we may have a WINS problem on our server which might just be responsible.

 

[gmmastros]

>> My knowledge of these services could be scrawled on a pinhead

That means you know more than I do. I'm glad it's worked out for you.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Glasgow]

Well I've resolved the WINS issue on the server and, believe it or not, I have now managed to connect in both directions by specifying the sql instances (rather than IP addresses).

I've done so much mucking about that I can't be sure exactly what steps resolved what problems and I still have a few problems with other servers to resolve. However, I think I have learned enough in the course of pursuing this thread to have the tools to resolve these. So thanks again.