Authenticating SQL connection using AD login details outside the domain

Question

I am trying to connect to a SQL server from outside the domain.

I have the following connection string:

SqlConnection("SERVER=Server;DATABASE=Database;User ID=Domain\User;Password=password");

When I try to use this outside the domain this will fail as it tries to log in using SQL authentication.

Is there any way to pass a command into the string to use Active Directory authentication.

What other ways would there be to connect to a SQL server, using active directory details outside of the domain?

Answer 1

You either need to:

1. Use Integrated Security in your connection string and create a domain trust relationship between your resource domain (where SQL server is) and your account domain (where the account is). Or...
2. Create an account in SQL Server and use that in your connection string as user name and password.

You're doing an invalid mix by putting domain credentials in user/pass and there's not even a trust relationship.

EDIT:

The comments indicate a need to sync SQL account with AD creds. This is not needed. A sql account is simply SQL scoped user name and password which is specific to SQL server and has nothing to do with domain creds. If you use SQL account, then those become a configuration setting in your app which is used to construct the proper connection and sent over the wire. SQL server authenticates those creds locally without involving AD.

It's also interesting whether you create a 2-tier (fat client accessing sql directly) or 3-tier (clients access your middle tier app server which accesses sql). If it's the latter, your application authenticates and authorizes users and after that, it uses a config setting with the sql specific user/pass in it to access sql. If it's the former and you're using sql accounts then you need one per client and that's a problem.