I have a server with a sqlexpress database on it.
It has 'Allow Remote Connections' checked
It has the Browser Service enabled and started utilizing Surface Area Configuration
It has Local and Remote Connections Using both TCP/IP and named Pipes.
I have used http://support.microsoft.com/kb/914277
And one machine can connect to it fine.. .
but others can't?
Any Ideas?
Casey
Hi Casey,
We'll need the exact error message that you're seeing to be able to help you, as it can be a number of reasons.
|||
PS. I can ping the server from both computers (the one that can connect and the one that cannot)
TITLE: Connect to Server
Cannot connect to HOZZZZZZZ\SQLEXPRESS.
ADDITIONAL INFORMATION:
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=-1&LinkId=20476
BUTTONS:
OK
|||you have already said that
(a) Remote connection enabled
(b) Browser service running
(c) TCP/IP protocol selected
the only one thing left is check for any Firewall enabled on this server. If yes create exception
also makesure the service is running
Madhu
|||Yes, we have checked that the service is running. (In SQL Server Configuration Manager when I click SQL Sever 2005 Services, both Sql Server and Sql Server Browser show state as 'Running' and Start Mode as 'Automatic)
Yes, there is no firewall runnning, so no need for exceptions.
(when I run firewall.cpl from command I get message box saying that the WIndows Firewall/ ICS service is not running.)
And this is why we can connect to this sql from some machines.. but not sure why we can't from others..
|||Can you connect if you specify ip instead of machine name? (e.g. 127.0.0.1\SQLEXPRESS)
If two of these machines are on the same domain, they would be able to connect with <single part name>\instance, but a machine outside of the domain would need either ip\instance or <fully qualified dns name>\instance.
Hope we can figure this out.
John
|||Actually, error 26 indicates that you've established the transport connection but cannot locate the named instance to which you want to connect. Is your client machine Vista by any chance?
Il-Sung.
|||
IP Name didn't work. (or fully qulalified name)
However we removed the \SQLEXPRESS and it seems to be working. (I double checked and the SqlServer configuration manager on the server does show that the it is running as a SQLEXPRESS Install.)
so Server = HOZZZZ\SQLEXPRESS - Doesn't work...
but Server = HOZZZZ - Does seem to work.. don't understand that one..
casey
|||Well if the named one did not work, you either have a client alias configured on your server or you have a default non-Express instance on your server. Connect to the instance and use the SELECT @.@.version to find out more details about the version of SQL Server.
Jens K. Suessmeyer
http://www.sqlserver2005.de
Again, are you using Vista?
|||The connection string without sqlexpress appears to be working.
Windows XP
select@.@.version =
Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)Oct 14 2005 00:33:37Copyright (c) 1988-2005 Microsoft CorporationExpress Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
Casey
|||Then the only explanation for me is that you are using a alias on the client side which points to the .\SQLExpress instance.
Jens K. Suessmeyer
http://www.sqlserver2005.de
|||I think the more likely event is that you clicked "default instance" when you installed SQL Express.
This may sound a bit confusing but if you click nothing on the instance selection screen but the "Next" button, SQL Express installs as a named instance titled SQLExpress. If you click "default instance", it will install as a "default instance" which has a different meaning.
In the global context, a default instance is run as MSSQLSERVER. The connection string to connect to a default instance is "machinename" with no instance name at the tail. For any named instance (including SQL Express's default named instance) you have to append \instancename, so it looks like "machinename\instancename".
The reason this is all confusing is that by default, SQL Express installs as a named instance, but if you click "Default Instance" it installs as a default instance (which is not what the default behavior was to begin with).
In a global context, the "Default Instance" is a special, unnamed instance that is the default for other SKUs of the product. This doesn't track with the local definition of "default instance" which, of course, should be the default on the screen (SQLExpress).
I hope that made sense and helps to clarify what might have been going on here,
John
No comments:
Post a Comment