Full Domain Trust and Natively Compiled Stored Procedures – In Memory OLTP


Recently one of my developers brought this specific problem to my attention. He was trying to create a natively compiled stored procedure(With Execute as Owner), and was getting an error –

ERROR:

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

My first reaction was to check the builds and to ensure that the user is running latest CU.(Not yet SP1 !) . The user was indeed running the latest SQL 2014 build.

I decided to repro this issue at my end, and I was able to create/compile the procedure without any errors.

My next troubleshooting direction was to check user permissions, and understand more about the environment where the user is running into this error.

The user in this particular scenario was using his domain credentials to create the SPROC. The account was something like  HQ\Developer. HQ is the domain and Developer is the user name.

SQL Engine Services was running under account LAB\SQL.LAB is the domain and SQL is the SQL Service Account.

I read more about Natively Complied Stored Procedures, and especially focused well on this MSDN article, Creating Natively Compiled Stored Procedures.

This portion of the article was my focus area(Underlying the main point) –

” Regarding EXECUTE AS and Windows logins, an error can occur because of the impersonation done through EXECUTE AS. If a user account uses Windows Authentication, there must be full trust between the service account used for the SQL Server instance and the domain of the Windows login. If there is not full trust, the following error message is returned when creating a natively compiled stored procedure: Msg 15404, Could not obtain information about Windows NT group/user ‘username’, error code 0x5.”

In my case there was one way trust between HQ and LAB, however LAB didn’t trust HQ. The error message which the user got was obvious.

There are multiple workarounds to solve this problem and its listed in the article too –

  • Use an account from the same domain as the Windows user for the SQL Server service.
  • If SQL Server is using a machine account such as Network Service or Local System, the machine must be trusted by the domain containing the Windows user.
  • Use SQL Server Authentication.

I decided to leverage a SQL account and the user was able to create the procedure with that specific account.

Conclusion – 

In memory OLTP is a very exciting feature and there are a lot of dependencies which you should be aware before deciding to deploy it on Production.

Thanks for reading and keep watching this space for more !

Advertisements

One thought on “Full Domain Trust and Natively Compiled Stored Procedures – In Memory OLTP

  1. I don’t see the connection between In-memory OLTP and this issue. Domain trust & impersonation seem to be the crux of this. IMO, HQ (assuming Headquarters) should not trust LAB.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s