2

Can I use the Integrated Security (a.k.a. Trusted Connection) option in a connection string if I'm connecting to a remote machine that's not on a domain?

I have a development server with Windows Server 2012 with WebSocket support, but I want to develop against the existing database on our main public server located in a remote virtual hosting environment. I've set up and established an encrypted VPN connection from the dev server to the main server, so I'm able to manager the database remotely via SSMS and connect to it via .NET's System.Data.SqlClient classes, but I'm currently doing so using an SQL Server account with a username and password.

Neither of the computers are on a domain, although the VPN simulates the connection occuring over a LAN. So I was wondering if there was a way to add the dev-server's windows user account to SQL Server on the main system, so that I could connect with integrated security from the dev server. Or does integrated security only work when connecting to the local database and a computer on the same domain?

Triynko
  • 18,766
  • 21
  • 107
  • 173

2 Answers2

3

Integrated security will only work when the machines are on the same domain (or a different domain with a trust) this is because SQL server has to contact a domain controller to authenticate a windows logon, which it can only do if it is a member of a domain.

Just being connected to the LAN is not enough as although the machine may be able to physically connect to the domain controller, it will not be able to authenticate users against it.

steoleary
  • 8,968
  • 2
  • 33
  • 47
  • Just to clarify. If they are on a LAN or VLAN, the authentication should work as long as they are on the same domain? But if they are not on a domain or are on different domains without a trust, then it won't work at all. Right? – Triynko Aug 15 '13 at 17:50
  • Yep, that's right, for integrated auth to work, the machines need to be in the same domain and be able to talk to each other and a domain controller. – steoleary Aug 16 '13 at 08:25
1

This is not necessarily true, here's how you can do it.

First add the Server name and IP address to your local hosts file.

Now go into Credential Manager in Control Panel and add the Server name of the SQL Server that you added to your hosts file with :1433 at the end.

So for example

local hosts entry

192.168.1.1 SQLServer

Control Panel Credential Manager

internet of network address: SQLServer:1433

Username: DOMAINNAME\UserName

This also works for Visual Studio if you want to use Integrated Security.

Hope this helps!

Andy B
  • 11
  • 2