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