Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to connect to sql-server with windows authentication from windows-service?

I have programmed a Windows Service in C# which should connect to an SQL-Server 2005 Express Database with System.Data.SqlClient.

As Microsoft prefers to use Windows Authentication over SQL Authentication I tried to connect to the database with Trusted Connection / Integrated Security.

However that doesn't work as I get a:

System.Data.SqlClient.SqlException: Login failed for user 'NT AUTHORITY\LOCAL SERVICE'.

Is it possible to login with a different Windows Account?

like image 580
räph Avatar asked Feb 11 '10 13:02

räph


People also ask

How do I connect to authentication mode in SQL Server?

In SQL Server Management Studio Object Explorer, right-click the server, and then click Properties. On the Security page, under Server authentication, select the new server authentication mode, and then click OK.

How do I configure Windows Authentication for SQL Server?

In the Object Explorer, right-click the server and click Properties. On the Security page under Server authentication, select SQL Server and Windows Authentication mode and then click OK. In the Object Explorer, right-click your server and click Restart. If the SQL Server Agent is running, it must also be restarted.

How do I connect to SQL Server using Windows Authentication in PowerShell?

By default, the SQL Server PowerShell components use Windows Authentication when connecting to an instance of the Database Engine. You can use SQL Server Authentication by either defining a PowerShell virtual drive, or by specifying the -Username and -Password parameters for Invoke-Sqlcmd.


1 Answers

At the moment, the service appears to currently run under the LocalService Account and this service account is not currently authorized on SQL.

The situation can be fixed in one of two ways: running the account under an account whichis authorized in SQL or add the LocalService account as a login in SQL. Specifically:

  • Change which account the service is ran as, in the Service management console. (ex: Computer Management | Services and Applications | Services then right-click for "Properties" on the service in question)
  • Alternatively, in "Microsoft SQL Management Studio", add the LSA account as a login and set this new principal (login) in a way that it can access the desired database objects.

Edit: The first approach is probably preferable, because the LocalService account is so pervasively found in the system that granting it access to SQL would expose SQL and the databases would a particular service or driver using it become compromised.
Instead by introducing a specific account one has more control over who accesses SQL objects and how. This of course bring the issue of configuring such an account, with regards to the privileges it should be granted, at the level of the system (not of SQL), and depending on what the underlying Service does, on may need to make this account rather powerful, hence a potential liability in other ways....

like image 146
mjv Avatar answered Oct 11 '22 16:10

mjv