#StackBounty: #sql-server #windows-authentication #impersonation Is it possible to run SQL Server as 'NT ServiceMSSQLSERVER' a…

Bounty: 50

For clarity, I’m asking how to resolve this

  1. without running SQL Server as a domain account and
  2. without making sa the database owner.

In a nutshell

If I install SQL Server 2019 and go with the recommended default user account of NT ServiceMSSQLSERVER, and then I connect and run EXECUTE AS LOGIN = [SOMEDOMAINsomeusername];, I get this:

Could not obtain information about Windows NT group/user ‘SOMEDOMAINsomeusername’, error code 0x5.

The problem goes away if I set the SQL Server service to run using a domain account, but I don’t want to do this. I like using the recommended NT ServiceMSSQLSERVER virtual account. I’m looking for a way to set this up that stays within the dev machine and doesn’t require collaborating by setting up a domain account or by manually configuring AD to recognize each development instance of SQL Server.

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-windows-service-accounts-and-permissions?view=sql-server-ver15#VA_Desc:

Use a MSA [managed service account] or virtual account when possible.

I’d prefer not to use an MSA because it requires setup outside the dev machine. So, is using a virtual account possible when I need to be able to do EXECUTE AS LOGIN = [SOMEDOMAINsomeusername];? What’s the configuration I’m missing in order to achieve this? If I’m asking for something impossible, could you explain the conflict?

More details

EXECUTE AS LOGIN = serves as a useful test in SQL Server Management Studio, but here’s what’s really going on: I’m running software that uses Windows authentication to log into SQL Server and create a new database, including an essential stored procedure that has WITH EXECUTE AS SELF. SELF is the domain account used by Windows authentication when the software connected to SQL Server and created the database. Attempting to execute the stored procedure fails with the same error message that EXECUTE AS LOGIN = does. (If it’s useful, I can explain why the software relies on a stored procedure having WITH EXECUTE AS SELF.)

I’ve seen similar questions asked before and get the answer, "just make the database owner sa instead of a domain account." I don’t want to do this. It’s a big hassle to have to maintain a password for sa and use it instead of Windows authentication every time I deal with this database.


Get this bounty!!!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.