2

I have a Console Application (C#) that connects to a database and sends some emails. It runs fine when a user debug in Visual Studio, but when I copy this to a server and run as a Service Account domain\AcctNotWorking I get the error below. If I switch the account that runs the Scheduled Task to my domain\login it works fine. Why is the service account trying to login as NT AUTHORITY\ANONYMOUS LOGON and any ideas how to fix this? The domain\AcctNotWorking is an admin on the server and has the appropriate permissions in SQL Server.

System.Data.Entity.Core.EntityException: The underlying provider failed on Open. ---> System.Data.SqlClient.SqlException: Cannot open database "MyDatabaseName" requested by the login. The login failed. Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. 

Connection String:  add name="ApplicationEntities" connectionString="metadata=res://*/ApplicationEntities" .csdl|res://*/ApplicationEntities" .ssdl|res://*/ApplicationEntities" .msl;provider=System.Data.SqlClient;provider connection string="data source=SQL-Server-Name;initial catalog=DatabaseName;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework"" providerName="System.Data.EntityClient"
Mamun
  • 66,969
  • 9
  • 47
  • 59
Carl Grzywacz MSFT
  • 23
  • 6

1 Answers1

1

Easy, Integrated Security=True means the current user will attempt to log into the sql server using their windows identity. You either have to 1) create a user for the service to run as, and configure the service to run it under this user, then give this user permissions in the database, or 2) create a sql login with a username/password and use that in your connection string.

Here's a starting point to learn about running a service under a user account

Minimum rights required to run a windows service as a domain account

and here's a starting point for creating sql server logins

https://learn.microsoft.com/en-us/sql/relational-databases/security/authentication-access/create-a-login

Although, honestly, that seems overly complex. All you need to do is login to the server using SSMS and right click on the Security folder and create a new login. It's pretty clear. Then you create a user in the database for that login (same process, but the security folder is under the database). Not too hard. Then switch Itengrated Security=True out for User Id=myUsername; Password=myPassword;

My preference would be to create a user account on the machine that has no rights other than what it needs to run the service, then create a login & user in sql server for this account that is also locked down to the bare minimum rights required to function. Doing this correctly can be relatively complex if you've never done it before. It is, however, rewarding and will gain you valuable experience you'll use to great effect in your career.

At least, if you go with the quick and dirty sql login method, encrypt your connection string: https://msdn.microsoft.com/en-us/library/89211k9b(v=vs.110).aspx

Community
  • 1
  • 1
  • It has to be mentioned though that using SQL logins is not necessarily what you want. Using a domain account is a valid option; it just has to be done properly. – Tipx May 19 '17 at 14:04
  • Depends, but it's generally okay if you do it right, such as using encrypted connection strings https://msdn.microsoft.com/en-us/library/89211k9b(v=vs.110).aspx –  May 19 '17 at 14:12
  • 1
    Oh, if the option is there, there are case when you want to do that. I didn't mean to say he shouldn't. Just that it's not the only good option. :-) – Tipx May 19 '17 at 14:16
  • 1
    True. It's the quicker option, but not the best. I'd also run under a specific account created for the service which is 100% locked down and has not a lick more rights than it absolutely needs in order to function. It's just a bit harder to do that than create a sql login and encrypt the connection string. (edited the answer to add these kinds of concerns and recommendations in) –  May 19 '17 at 14:22
  • Shouldn't it try to login to the sql server as the account the Service Account 'domain\AcctNotWorking' that the Scheduled Task runs as? – Carl Grzywacz MSFT May 20 '17 at 13:54
  • @user8036781 It's starting to sound like this isn't as much a programming issue as it is an infrastructure issue. Is the database on a remote server? Are you logging in using NTLM? You might want to run the application on the database machine instead, which would take many of these other issues out of the equation. –  May 22 '17 at 13:27