I have a huge connectivity issue that I can't seem to solve.
We're using SQL Express Edition as a local data store that is participating as a subscriber to SQL Server Enterprise Edition. Perfectly valid and supported.
Now the issue. I have the Express Edition on one machine connected just fine, but the other one absolutely refuses. Here is what I did for the one that connected correctly:
1. Enable remote connections for the Express Edition instance and restart the instance
2. Added the SQL Agent service account on the server to the SQLServer2005MSSQLUser$machine$SQLEXPRESS group on the local machine (yes, I want it to have that level of authority)
3. Connected to the machine with Express Edition to the network via VPN.
4. On the server, opened Management Studio and connected to the Express Edition on the machine to verify connectivity
Everything works.
On the machine that refuses, I've duplicated the steps above and it absolutely refuses to connect. I have verified that remote connections are enabled. I have tried connecting via both name as well as IP, just to rule out DNS resolution - neither works. I don't have any security issues. I can go all the way to mapping the c$ share on the machine with the Express Edition directly from the desktop of the server. I can browse for SQL Servers on the network and the machine shows up in the list, so I know that it is being recognized as having an instance of SQL Server. I can't find a single, solitary reason why it should throw a login failure. The message that I'm getting is as follows:
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) (.Net SqlClient Data Provider)
Does anyone have any ideas?
Yes, before anyone asks, the SQL Browser service is running.|||Hi Michael,
Have you configured firewall exceptions on the machine where you're having the problem? You need to make excepitons for both SQL Server and SQL Browser.
Mike
|||Yes, we have firewall exceptions configured. I even went to the step of shutting down the firewall completely to verify that wasn't a factor in the connectivity issues.|||Just to verify...
Can you connect to SQL Express from the local computer?|||
1. Yes, I can connect to Express from the local computer
2. Yes, I have tried a remote connection from another machine and get the same error message
3. Yes, the name is spelled correct including the backslash.
More info from the troubleshooting checklist:
Client: Windows XP Pro SP2 with hotfixes and SQL Server Express Edition
1. Connection string for the local app: I'll add to this when I can get it pulled from our custom application.
Connection string for the remote app is SQL Server Management Studio.
2. The local client connects without any problems. The remote client (Management Studio) throws the following error message:
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) (.Net SqlClient Data Provider)
3. The client application in #2 is local to the SQL Server Express instance. The connection from SQL Server Management Studio is remote.
4. Yes, I can ping - the client from the server and the server from the client - both directions using both IP address as well as name
5. I can telnet from the client to the server as well as from the server to the client. I can map an admin share on the server from the client and map an admin share on client from the server.
6. The database provider for the local application is ADO.NET 2.0 inside a custom application developed in C# with Visual Studio 2005. Remotely, it is Management Studio.
7. All machines are in the same domain. I log into the console on all machines as a local administrator, builtin admins is still a member of the sysadmin group in SQL Server, so I do not have any security (authentication) issues in the way.
8. Shared Memory, Named Pipes, and TCP IP are all enabled. Remote connections are enabled using TCP IP only.
9. There are no server aliases configured
10. No, encryption is not forced. Communication is unencrypted at this point.
Server side:
1. SQL Server 2005 9.00.1399.06
2. Enterprise Edition
3. Shared Memory and TCP IP are enabled. Named Pipes and VIA are disabled
4. Yes, the server starts successfully on both the server and the client
5. Yes, the SQL Browser service is started on both the server and the client
6. SQL Server Express Edition is running under Network Service. SQL Server Enterprise Edition is running under a domain account. I am connecting to the Enterprise Edition on the server using my Windows credentials which are a member of domain admins. I have tried both my Windows credentials as well as a SQL Server standard login to connect via Management Studio from the server to the Express Edition on the client and neither works, giving the same error message specified above.
7. Yes firewall exceptions have been made. I've even taken the step of disabling the firewall on both sides to rule it out.
8. Yes, firewall exceptions have been made for both SQL Server as well as SQL Server Browser. TCP 1433 and UDP 1434 have exceptions in the firewall on both sides. And I've even disabled the firewall on the client and routed around it on the server to rule out a firewall issue. (I can connect remotely to SQL Server Express Edition on any machine that is running the Express Edition and also other instances of SQL Server. I can NOT connect remotely to any machine that has JUST SQL Server Express Edition installed on it.)
Platform:
Client - Windows XP Pro SP2 + hotfixes
Server - Windows 2003 R2 SP1 + hotfixes
Misc.
No certificates
I have been through every blog that I could find. Every KB article, website, and scoured mutliple newsgroups and mailing lists. I have been through every troubleshooting checklist that I could find anywhere. None of them work. I spent 4 solid days working on this connectivity issue. I know I have everything configured correctly for firewall, browser, protocols, security, and I am not misspelling anything. I have been working with SQL Server 2005 since before it hit Beta 1 (I wrote a whitepaper for the Beta 1 Readinesss Kit, reviewed several others, and also worked on the Relational Engine labs that Microsoft used during the Technical Airlift and several other internal/external launches.) I know how to troubleshoot an issue and I'm going to do everything possible and rule out everything I possibly can before I ever post asking for help. I am also a moderator on these forums for SQL Server for a reason.
|||Hi Michael,
It seems like you're doing everything right, I'm a bit baffled. I have only one thought...
8. Yes, firewall exceptions have been made for both SQL Server as well as SQL Server Browser. TCP 1433 and UDP 1434 have exceptions in the firewall on both sides. And I've even disabled the firewall on the client and routed around it on the server to rule out a firewall issue. (I can connect remotely to SQL Server Express Edition on any machine that is running the Express Edition and also other instances of SQL Server. I can NOT connect remotely to any machine that has JUST SQL Server Express Edition installed on it.)
You mentioned that you have created an exception for 1433, but SQL Express uses dynamic TCP by default so it's possible, even likely that it is not using 1433. I generally open an exception for the applicatin itself rather than the port by adding sqlservr.exe to the list, you might want to try that.
Lest you think I didn't read your whole post, I did notice that you completely turned off the firewall at one point, which should have overcome any issue related to dynamic ports, but like I said, it seems like you're doing everything right. I'm grasping at straws here. 
Mike
|||Well, I'm even more baffled at this point. I rebooted this laptop for the umpteenth time and now it connects without any problems at all. I still have one more to work on, but have to wait until he connects up to our VPN again.|||One step forward and one step back. I have one of the machines that all of a sudden decided to connect. I still have another machine that refuses to connect. All of the information previously posted in this thread applies to the machine that still refuses to connect.|||So, it gets even weirder. To make this clear, let me go back to ground zero and give you the entire configuration that I'm dealing with.
Server - SQL Server 2005 Enterprise Edition
The server is a publisher and the SQL Server Agent is running under a domain account named sqlservice. For example purposes, my domain account is mhotek and is configured as a domain admin. I have configured a merge publication on the server and configured the security for the snapshot and merge agents to impersonate the SQL Server Agent Account (sqlservice). (Yes, I want it set this way very specifically at this point to elminate moving parts.)
Client - SQL Server Express Edition (doesn't matter if it is Gold or SP1 same thing happens)
On the client I have turned on remote connections and restarted the service. I have started the SQL Browser service. I have added domain\sqlservice to the SQLServer2005MSSQLUser$client$SQLExpress group.
Now the innteresting observations:
1. I logon to the Server with my credentials and User1 logs on to Client. I open SSMS on the server and can NOT connect to client\SQLEXPRESS and get the error message of:
TITLE: Connect to Server
Cannot connect to client\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
I also can NOT create a subscription to this machine, because of the same error message above. i.e. it can not find it.
2. I log User1 off the client and log on using my credentials (mhotek). All of a sudden, I can use SSMS on the server to connect to client\SQLExpress. I can create a subscription. The merge agent (running under sqlservice) can push the snapshot and replicate any changes.
3. I log mhotek off the client and log on User1. All of a sudden, I can not connect to client\SQLExpress via SSMS from the server and the replication engine can not connect.
In short, if I log on to the client machine that has SQL Express on it, all connectivity and functions work. If anyone else logs on to the client machine with SQL Express on it, no one can connect to it remotely via any mechanism. The weirdest part of all of this is that the security credentials for replication are NOT my credentials, but are instead the credentials of sqlservice. However, replication does not work unless I am logged on to the client with my credentials (mhotek).
The client is Windows XP Pro SP2 and the server is Windows 2003 Enterprise with all service packs and hot fixes applied. Client is SQL Server 2005 Express Edition Gold (1399) and the server is SQL Server 2005 Enterprise Edition Gold (1399). (Yes, I've tried this with SP1 on both sides and get exactly the same results.) This is NOT a user instance of SQL Express Edition.
Any ideas, because I'm completely baffled and can't even dream up what might be causing this.
|||We can close this one out. It turned out to be two things halting the SQL Server connections. The first was an incorrect WINS scope. The second issue was that DNS registration was not occuring.
No comments:
Post a Comment