How to give SQL access to NETWORK SERVICE account?

Question

I am trying to create a schema and a table that is accessible by a Windows Service that I wrote, but my service is unable to gain access to it...

USE [tempdb]
GO

CREATE SCHEMA [blah]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [blah].[domains](
    [id] [int] NOT NULL,
    [name] [varchar](256) NOT NULL,
 CONSTRAINT [PK_domains] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

A very simple table.. When I try to SELECT it via my Windows Service, I'm getting:

System.Data.SqlClient.SqlException (0x80131904): The SELECT permission was denied on the object 'domains', database 'tempdb', schema 'blah'.

I've tried switching my Service account from LocalService to NetworkService but it gives the same thing.

The connection string I am using for my service:

connectionString="Data Source=.\SQLEXPRESS;Database=tempdb;Integrated Security=True;MultipleActiveResultSets=True"

I've also tried to create a new Logins for NT AUTHORITY\NETWORK SERVICE, then a user NETWORK SERVICE mapped to that Login and then grant permission on blah as well as domains table, but still not working.

PS: The same code was able to access the table when I ran it as a normal executable, but not when is running as a Service.

I think when a windows service runs, it uses the same of the server concatenated with a '$' - so MyServer$ would be the login it uses when traversing your network (assuming the service and the database are on different servers). Have you tried granting access to MyServer$, explicitly? — Stan Shaw, Jul 11 '17 at 16:20
That is what I do - and it works for folder permissions as well as SQL permissions. — Stan Shaw, Jul 11 '17 at 16:57
@StanShaw where/how can I do that? I don't see any option to grant that under my table nor user under SQL Management Studio when I check the table properties. How can I do that via a sql script? — codenamezero, Jul 11 '17 at 17:05
In SSMS > Security > Logins....You'll have to navigate to the appropriate user in there - you will most likely need to add `Domain\NETWORK SERVICE` or `Domain\SYSTEM` or something like that. I was wrong about the $ being available to SQL Server - that's only on file-level permissions. — Stan Shaw, Jul 11 '17 at 17:10
Or you can easily configure your service to run as a windows account (which you can create, if necessary) - and then add that account as a SQL login - and provide whatever credentials are necessary. I think that's your best bet, so I'll post it as an answer. — Stan Shaw, Jul 11 '17 at 17:13
i used a lot NT AUTHORITY\NETWORK SERVICE with MSSQL, so there shouldn't a problem. Just guessing: did you assigned server role (at least) public? BTW, be aware that tempdb can be recreated at restart. I believe it would better to create your table elsewhere. — deblocker, Jul 12 '17 at 16:17
If I set my table's permission to public, then it works. But I'm not sure if this is the right way to go. — codenamezero, Jul 12 '17 at 17:22

score 0 · Answer 1 · answered Jul 11 '17 at 17:14

We discussed a few approaches in the comments, but I think your best bet is to:

Create a Windows account (or use an existing one)
Configure your service to run as that Windows account
Add the Windows account as a SQL login
Grant the desired permissions to that SQL login

How to give SQL access to NETWORK SERVICE account?

1 Answers1