Is it possible to get "NT AUTHORITY\NETWORK SERVICE" user independent of language?

Question

I have encountered today a problem that I have never faced before.

I have developed an application that uses SQL Server database. That application has a Windows Service that accesses the database.

Since the Windows Service uses Network Service as the user, I need to add that user to the database so that it has access to it.

That task is accomplished automatically by an installer I have also developed.

In that installer, I have this script fragment:

USE [MyDB]
GO

IF NOT EXISTS(SELECT principal_id FROM sys.database_principals WHERE name = 'NT AUTHORITY\NETWORK SERVICE')
    BEGIN
        /****** Object:  User [NT AUTHORITY\NETWORK SERVICE]    Script Date: 26-10-2018 13:42:57 ******/
        CREATE USER [NT AUTHORITY\NETWORK SERVICE] FOR LOGIN [NT AUTHORITY\NETWORK SERVICE] WITH DEFAULT_SCHEMA=[dbo]

        ALTER ROLE [db_owner] ADD MEMBER [NT AUTHORITY\NETWORK SERVICE]
    END

That script works almost always, but today installation.

Today installation was made in a Windows 7 PC which is in Spanish. The installer sent an error telling that "NT AUTHORITY\NETWORK SERVICE" user does not exist.

Looking at the issue, I found that in that PC, that user is called "NT AUTHORITY\Servicio de Red", that is, "NETWORK SERVICE" in Spanish.

That is strange because I have other PC's with Windows 10 in Spanish, but in that O.S., the user is called "NT AUTHORITY\NETWORK SERVICE" too.

To solve the issue in that PC, I had to install SQL Server Management Studio only to assign "NT AUTHORITY\Servicio de Red" user to the database.

Since I don't know the user name beforehand, is it possible to add to SQL a generic user that will work everywhere?

Answer 1

Yes, it is possible. To solve the issue of making sure that your are referencing the NT Authority\Network Service account independent of the OS language, there is a reference at https://support.microsoft.com/en-us/help/243330/well-known-security-identifiers-in-windows-operating-systems which identifies the SID of this account. It is defined as

SID: S-1-5-20
Name: NT Authority
Description: Network Service

Note: the numbers seem to be expressed as decimal numbers.

If in your SQL Server management studio you select from sys.server_principals:

select * from sys.server_principals

you'll see that NETWORK SERVICE has a SID value of 0x010100000000000514000000 the '514' (this is hexa) part corresponds to the 5-20 (decimal).

If you check the following statement in a query window:

select quotename(SUSER_SNAME(0x010100000000000514000000))

you'll see the result: [NT AUTHORITY\NETWORK SERVICE]

With this in hand, your original creation statement becomes:

DECLARE @user nvarchar(50)
DECLARE @SQLStatement nvarchar(500)
SET @user = quotename(SUSER_SNAME(0x010100000000000514000000));
SET @SQLStatement =
N'IF NOT EXISTS(SELECT principal_id FROM sys.database_principals WHERE name = ''NT AUTHORITY\NETWORK SERVICE'')
  BEGIN
      CREATE USER [NT AUTHORITY\NETWORK SERVICE] FOR LOGIN ' + @user + N' WITH DEFAULT_SCHEMA=[dbo]
      ALTER ROLE [db_owner] ADD MEMBER [NT AUTHORITY\NETWORK SERVICE]
  END'
EXEC sp_executesql @SQLStatement;

And you'll get the desired created account.

Cheers.

Answer 2

Don't use NT service account.

Create a local windows or domain account. Add it as login to SQL Server and as user to database you need. And change your windows service application to run under that account.