11

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?

Dale K
  • 25,246
  • 15
  • 42
  • 71
jstuardo
  • 3,901
  • 14
  • 61
  • 136
  • Is your windows service running as a standalone service or in IIS? If in IIS, you could use the Application Pool Identity feature. Second question would be, are you running this on a single domain? If so, you could create a service account in the domain and set the service to run under this account, then you'd just need to add that service account to the DB in your installer. – Brenton Jun 04 '19 at 14:09
  • 1
    A much better option is to run the service under a virtual service account (`NT SERVICE\`). You do not need to create these users in advance, they always have the name of the service (so completely predictable) and Windows will automatically manage the passwords for them. – Jeroen Mostert Jun 04 '19 at 14:32
  • Your question *does* have a literal answer, by the way (you can get the local name of this account in SQL Server using its well-known SID), but I'm hesitant to provide that because it's so obviously the Wrong Thing to do. – Jeroen Mostert Jun 04 '19 at 14:42
  • @Brenton it is a standalone service that should run with highest priviledges possible... that is way I cannot create a new user. The only solution I could see is to create a SQL Server user, but that has the disadvantage that it will depends on SQL Server configuration. Not all customers allow SQL Server and Windows authentication. – jstuardo Jun 04 '19 at 16:59
  • @JeroenMostert Can you explain that further? is that user has full priviledges for all operating system resources? – jstuardo Jun 04 '19 at 17:00
  • No, exactly the opposite -- virtual service accounts have *no* privileges other than those you give to them, and that's a good thing. Please note that `NT AUTHORITY\NETWORK SERVICE` does not have "full privileges for all operating system resources" either -- it was introduced specifically because it *doesn't*. You may be misinformed about what your service truly needs. Any service that runs with permission to do anything is a security problem waiting to happen. – Jeroen Mostert Jun 04 '19 at 17:20
  • @JeroenMostert instead, I can use LocalSystem account.... but in any case, I cannot use a virtual service account as you suggested. "LocalSystem" account is other account that is also translated. – jstuardo Jun 04 '19 at 17:27
  • @JeroenMostert or maybe you can suggest other user. I have found that in order to write to the application folder insite Program Files folder, I need administrator permissions. If you can suggest a user that will allow me that, it is sufficient. – jstuardo Jun 04 '19 at 17:29
  • *Any* user can do that if you give the user *permission* to do that (and [just that](https://en.wikipedia.org/wiki/Principle_of_least_privilege)). You can use a tool like `icacls` to grant write permission on the specific folder you need. Do not just pick an admin account because they happen to already be able to do that, and *especially* not LocalSystem. Your service does not need to be able to format the hard drive, steal the user's credit card data or serve as a malware backdoor for a zero day hack, right? But that's what you allow when you make it LocalSystem. – Jeroen Mostert Jun 04 '19 at 17:35
  • Can you run a script to check user running for example dnscache when installing your application powershell: Get-WmiObject win32_service | where name -eq "Dnscache" | ft "StartName" – Piotr Palka Jun 05 '19 at 16:23
  • @JeroenMostert maybe you don't understand the real problem. I can NOT create a new user. I will always have problems because companies always has restrictions. I need to create an installer that can configure a Windows Service to run as Localsystem or NetworkService account. So, the problem is reduced just to allow that user to access the database. I can do that in SQL Server script, but the problem is that the user name depends on the O.S. language. If I use any account is not the solution also because I cannot make the Windows Service to depend on an account that some day can be removed. – jstuardo Jun 10 '19 at 12:52
  • My company has a restriction about using one user account for one thing. You would need to create a new account for your application. You would not be allowed to use any existing account. – HackSlash Jun 10 '19 at 20:57
  • Virtual service accounts do not need to be created explicitly; they are managed by the OS and subject to no restrictions a company could impose on you. I'm also not aware of any local policy any company could impose that somehow restricts the creation of new local accounts, if you *had* to create one. I understand what you *consider* the real problem, and I don't dispute that has a solution as well. – Jeroen Mostert Jun 11 '19 at 10:24

2 Answers2

8

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.

Cristián Ormazábal
  • 1,457
  • 1
  • 9
  • 18
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.

Daniel N
  • 1,122
  • 1
  • 8
  • 14
  • That is not the option. I need the service to run as the highest security level. If I create a new user, that user will have problems when accessing more restricted resources. – jstuardo Jun 04 '19 at 16:56
  • 1
    don't see an issue here, create a windows account that has elevated privileges. run your app under new "service" account and run sql server under network service (not the best idea) – Daniel N Jun 04 '19 at 18:59
  • What is not the best idea? to create a new user? In any case, that is a big issue. You are assuming I will have full access to the machine, and that is not always the case. A PC or Server that is part of a company, normally is very restricted. They will not allow to create Windows users. Furthermore, the installation of the software should be very straight forward. I have an installer that should do everything. If I try to do actions that require some other sort of permissions, installer will fail for sure. That is why I need to use what I know that already exists in every computer. – jstuardo Jun 05 '19 at 23:48
  • it's not a good idea to have sql server run under network service. if you install sql server stand alone, latest versions, notice that it uses some other account name like machinenamesql$, which is a service account and not Network Service. you installer can possibly do the same. – Daniel N Jun 05 '19 at 23:49
  • I am not running SQL Server as Network Service. Why do you think of that? – jstuardo Jun 05 '19 at 23:50
  • my apologies, i assumed that, since you noted that your app must also run under network service to gain access to both installation dir & sql server. – Daniel N Jun 05 '19 at 23:55
  • Yes,,,, my own windows service should have access to both resources.. it should be able to write to the folder where the application is installed (under Program Files folder) and to read/write the database tables. That is why I need to know the exact user ID in order to add it to the database using the installation script. If that user ID changes depending on the operating system language, I am lost. – jstuardo Jun 07 '19 at 00:12