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.

Answer 1

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