2

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 Service\MSSQLSERVER, and then I connect and run EXECUTE AS LOGIN = [SOMEDOMAIN\someusername];, I get this:

Could not obtain information about Windows NT group/user 'SOMEDOMAIN\someusername', 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 Service\MSSQLSERVER 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 = [SOMEDOMAIN\someusername];? 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.

3
  • The SQL Server service identity is largely unrelated to its Authentication system. You'll just need to add an SPN to allow SQL Server to authenticate itself with AD to allow for impersonation like this. That said, it should be able to do it via the "machine account" (COMPUTERNAME$). – Dai Sep 5 '20 at 5:21
  • I think you're misreading that recommendation. An Managed Service Account is an Active Directory Domain User Account. Microsoft is not not recommending you always use NT Service\MSSQLSERVER - it's saying that it is also recommended to use an AD domain user account for SQL Server. See here: mssqltips.com/sqlservertip/5334/… – Dai Sep 5 '20 at 5:24
  • @Dai The SQL Server log says "The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/COMPUTERNAME.SOMEDOMAIN.local ] for the SQL Server service." Is that similar to doing via COMPUTERNAME$? Thanks for the clarification. For both your comments: 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. – jnm2 Sep 5 '20 at 16:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.