none
Connecting to SQL on a domain from a workgroup

    Question

  • 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>


    Paddy

    Sunday, August 04, 2013 11:45 AM

Answers

  • 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.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Sunday, August 04, 2013 1:28 PM

All replies

  • 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.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Sunday, August 04, 2013 1:28 PM
  • 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


    Paddy

    Sunday, August 04, 2013 2:22 PM
  • 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


    Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

    Sunday, August 04, 2013 6:16 PM
  • 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


    Paddy

    Sunday, August 04, 2013 6:50 PM
  • 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.

    Sunday, August 04, 2013 6:58 PM
  • 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.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Monday, August 05, 2013 12:28 AM
  • 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


    Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

    Monday, August 05, 2013 1:21 PM