I have set up a new installation of SQLExpress 2012 on a Windows Essentials 2012 Server. <o:p></o:p>

I have set it up with mixed authentication, and whilst testing I am using the sa user. <o:p></o:p>

I have a small office network, which I have been running as a workgroup (15 or so PCs and printers).<o:p></o:p>

Eventually I will migrate these to a domain, but it will take a bit of time for me to do this. In the interim I need connect to the SQL server from the workgroup.<o:p></o:p>

I have one PC which is joined to the domain, when I login to the domain, I can connect vie Management Studio. However when I login, as the local user, I get the error<o:p></o:p>

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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 1326)<o:p></o:p>

Interestingly, I can find the server when I browse for servers. <o:p></o:p>

So my question, is how I can connect to the domain SQL server from outside the domain (in this case a workgroup, not another domain) please? <o:p></o:p>

So my question, is how I can connect to the domain SQL server from outside the domain (in this case a workgroup, not another domain) please?

For PCs and users accounts that are not domain members, I strongly suggest you continue to use SQL authentication because that is why SQL authentication exists.  You can create a SQL login for each individual user and control authorization via user and database role membership until you fully migrate to the domain infrastructure.  This will also make it easer to transition to Windows authentication too because the user security authorization will already be in place.  To move to Windows authentication, add the users to an AD group, create a login for that group in SQL server, add the group as a database user and member of the same database role.

I expected a "Login failed" error instead of the one you are getting because the Windows domain will not honor the untrusted security credentials of the local account.  But the symptoms might vary depending on a number of factors.  Although I'm not recommending it, there is a hack you could try.  Create a domain user with the same name and password of the local user.  Add that domain user account as a login and database user.  This might work depending on the Windows server and client security settings (i.e. allow NTLM authentication).  Personally, I wouldn't go there.

Thanks

I really need to fix the error rather than find a work round, as once I have proved the connection works, I have to install a third party product, which manages it's own security, but which I will have no ability to change.

Paddy

Hello Paddy,

You can connect to a SQL Server instance with Windows authentication from another computer only if the both computers ( on which SQL Server is installed and the other one on which the application is running ) are belonging to the same Windows domain managed by a domain controller ( I am eliminating the case where SQL Server is installed on the same computer than where the application is running ).

So , if you are unable to modify your connection string , you have only one choice : to use the SQL Server authentication. 

There were several threads  related to this problem in this forum and I have seen only the Dan's proposal as a valid workaround even if it is a little complicated solution.

Please, do you know the name of this third party product about which you wrote in your 2nd post  ( if yes , could you provide its name ) ? Maybe , it includes a workaround for your problem that it could be used during the install and configuration of this product.

Have a nice day

Hi,

sorry, I think I have been unclear, I am using SQL authentication, but for some reason I am getting the error msg posted earlier, when I do try to connect. I am not trying to use a windows account to login. Sorry for the confusion.

I am assumming it is something in my network setings, but I can not see it.

Thanks

Paddy

Hi

Possibly try using runas with the /netonly option e.g.

runas /netonly /user:yourdomain\youruser ssms.exe

You can find more info on using this method on the web.

sorry, I think I have been unclear, I am using SQL authentication, but for some reason I am getting the error msg posted earlier, when I do try to connect. I am not trying to use a windows account to login.

If I understand your current situation, you can successfully connect using SSMS with the sa account when you login the PC using a domain account.  But if you log out and back into the same PC as a local user account and try to connect to the same server, you get the network-related error message? 

Make sure SQL Server is configured to listen on TCP/IP.  Unlike TCP/IP, named pipes is an authenticated protocol so Windows gets involved in authenticating the user before the SQL connection is made.

Hello,

I am thinking that there are 2 questions in your thread.

The 1st question is related to your error message about error 40 ( Named pipes ) . I would suggest you to have a look at :

http://blogs.msdn.com/b/sql_protocols/archive/2007/05/16/named-pipes-provider-error-40-could-not-open-a-connection-to-sql-server-microsoft-sql-server-error-xxx.aspx

http://blogs.msdn.com/b/sql_protocols/archive/2007/03/31/named-pipes-provider-error-40-could-not-open-a-connection-to-sql-server.aspx

These 2 articles are from the SQL Server Protocols Blog which is the REFERENCE about any SQL Server connection error. Even if they are not recent , they are always useful and a simple look at this blog should be enough to find the solution to a connection error  in 90% of the problems

Blog address : http://blogs.msdn.com/b/sql_protocols/

The 2nd question is related to "Eventually I will migrate these to a domain, but it will take a bit of time for me to do this. In the interim I need connect to the SQL server from the workgroup". It is not clear what you mean with "domain" as for most people , domain is implying a Windows domain managed by a domain controller ( on a Windows Server ). See for example :

http://forums.anandtech.com/showthread.php?t=1286911

Anyway , it would be useful to know the exact version ( year + last installed service pack ) and edition of your small office as it could help us to find an appropriate solution.

If you are using the SqlConnection class to connect , you should display the full message and the following properties of the SqlException object generated automatically when an exception is occurring with any method of the SqlConnection class

- Class

- ErrorCode

- HResult ( available only since .Net Framework 4.0 )

- Number

- State

With these values , it is possible to reduce the possible origin of connection errors.

Have a nice day

As usual, the purported "answer" is no answer at all.

Fact: Connecting to the SQL Server on the domain from a PC on the domain works fine with Named Pipes.

Fact: Connecting to the SQL Server on the domain from a PC on a workgroup will not work with Named Pipes.

Fact: SQL Server 2012 installs with TCP/IP disabled by default.

1. Alter the domain SQL Server configuration to enable TCP/IP.
2. Add exceptions to the domain SQL Server firewall to allow SQL Server connections.
3. Add exceptions to the workgroup PC firewall to allow SQL Server connections.
4. Force the workgroup PC to use TCP/IP. Easier said than done.

Even then it might not work. It just seems like they release SQL Server Express as a simple database solution but then require us to have MCSE level of knowledge to configure it for basic remote access operation. What we need is one of those "Fix It For Me" links to sort this all out for us.