cannot connect to sql server express from sql server standard

Question

... like my title says... I cannot connect to my instance on sql server express from sql server standard... I have tried disabling firing wall and checked sqlbrowser is started but for some reason I cannnot connect to my datbase... called server_name\sqlexpress..

I have a virtual machine and a full scale MS SQL Server 2008 R2 running on it... and I have several other vm running sqlexpress. they run fine and I can connect to them using sqlexpress... but when i try to access from sqlserver... I get this error.

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

Digging deep into the error, I found this

Error Number: -1 Severity: 20 State: 0

and finally this...

Program Location:

at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at Microsoft.SqlServer.Management.SqlStudio.Explorer.ObjectExplorerService.ValidateConnection(UIConnectionInfo ci, IServerType server)
at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()

Firewall is turned off on the VM that's running mssqlserver... I turned of firewall on one of the vm that's running the sqlexpress but I still get the error... can someone please help... thank you

Answer 1

Ensure that you have the TCP protocol enabled for the database instance. Microsoft provides detailed directions here.

So much for the short, simple answer...

To me, the error message implies that SQL Server Standard can't find the hostname in DNS, it can't interact with the sqlbrowser service properly, sqlbrowser doesn't know about the instance for some reason or Standard can't connect to the proper port on the Express computer.

What we can take off of the table are things like a bad username, an incorrect SQL password or a missing default database, which would be noticed after connecting. The connection process isn't getting far enough along for a failure like those things to happen.

I'm assuming that all of your VMs are on the same server and that we don't have to worry about hardware firewalls or NAT redirection since the VMs are all on the same virtual network.

The thing that strikes me is that you can connect from the Standard computer to all but one Express computers. What is different about that particular computer? I presume that the broken one has some network functionality, like it can see other computers. Is the problem computer on a different virtual network than the other computers?

Some troubleshooting tasks: First, figure out what the IP address for the Express computer is.

Next, connect to the SQL Server Standard computer using Terminal Services, Remote Desktop, vSphere or something similar. Can you ping from SQL Server Standard to SQL Server Express? First, try pinging the hostname for the Express computer, then try using the IP. If you can ping by hostname and by IP, double-check that the shown while pinging by hostname is the same as IP address that is assigned to the Express computer. (You might be pinging some other computer, having gotten "lucky".) If the IP numbers differ, you may have some sort of network mis-configuration of your hypervisor.

If you can't ping by hostname and you get an error about the hostname not being found, try a fully qualified domain name. If your hostname is "express", try "express.foo.com", or whatever seems appropriate. If something works, try connecting to the instance with that as a hostname.

If you can ping by hostname and the IP numbers match, the Express computer should properly registered in DNS.

If you can't successfully ping by hostname but you can by IP, try connecting with SSMS from the Standard computer with the IP address of the Express computer rather than the hostname (i.e., rather than "hal9000\sqlexpress", try "192.168.1.100\sqlexpress". If that works, there is something up with the networking.

Leaving that in place isn't a good long-term solution because IP numbers change more frequently than hostnames, but it might be OK for testing/development or until you can figure out why the Express computer isn't in DNS the way you think it should be.

If the pinging works OK, can you use SSMS on the Standard computer to connect to the instance on the Express computer? If you can, the problem probably has to do with how the linked server was defined.