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

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.

Answer 2

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.