Sql-server – SQL Server: Mixed mode security: Can a non AD user (a local user, remote client) have a trusted connection to SQL

active-directorysql serverweb service

I have used SQL Server trusted connections for years. It works well when the client app is a domain user.

Imagine this three server config:

  • dbserver
  • webserver
  • domain controller

I now have a situation of a web app that can no longer run on as a domain user (long story). This is a problem because this app stores its db connection string in the registry. If I use SQL auth, the SQL password is in clear.

Is it possible to ?

IOW: Is it possible to add webserver\localuser to SQL server as a trusted user, or can SQL server only trust domain users?

Best Answer

I now have a situation of a web app that can no longer run on as a domain user (long story).

The web server can't run under the account or the application context can't run under the account? For example, the web server is sitting in a DMZ?

This is a problem because this app stores its db connection string in the registry. If I use SQL auth, the SQL password is in clear.

This is a problem with how the password is being stored, not a problem with SQL Server TBH. However, you can overcome this by encrypting the password at rest and decrypting on demand then freeing the memory - for example.

The password is not sent over the wire in plain text, this encryption when connecting to SQL Server through SQL Authentication is completed for you automatically.

Is it possible to ?

It is not possible to connect to SQL Server with a username/password combo and setting the trusted connection setting. This is because trusted connection doesn't mean "encrypted and secure connection" it actually means "use windows tokens instead of a username and password". It doesn't mean the connection isn't trusted it just means use my token which will either end up being protected through ntlm or Kerberos.

Is it possible to add webserver\localuser to SQL server as a trusted user, or can SQL server only trust domain users?

There is no such thing as a "trusted user or login". Local windows accounts are only known to the local server, thus you could not use webserver\localuser unless the database server was also on the webserver. Since it is not, this is not possible. Since the local user is not in active directory, this will also not work. Thus, no, you cannot use that method.

Your best possible scenario for this is to use encryption of the connection string at rest and potentially even SSL connections (though that might be overboard and doesn't make the password kept in the registry and more or less secure, it just encrypts the traffic on the line).

Related Question