WCF Service Cannot Login To Remote Database

Question

I have a WCF service which is hosted in a Windows Service. This Windows service is set to run as LocalSystem. This WCF attempts to login to a remote SQL Server 2008 Express database but the login fails.

The exception error states:

Cannot open database \"{database name}\" requested by the login. The login failed. Login failed for user '{domain name}\{machine name}$'.

I've replaced the database, domain and machine names with tags but you get the idea.

Any idea how I solve this? I thought of adding this user to SQL Server logins but I can't find this user in its properties.

Answer 1

I would suggest creating a new user account on the sql server, and then impersonating that account from your windows service.

The WindowsIdentity class has many constructors which you can use to create a WindowsIdentity of the user you expect SQL server to allow to login (http://msdn.microsoft.com/en-us/library/e599ywa6.aspx).

Have you tried to change the user of the thread to one recognized by the SQL server as a valid user by using the WindowsIdentity.Impersonate Method?

I believe you would put the code to impersonate the user in the service class that is being hosted by the windows service, not in the client code accessing the service.

For details and an example, See: http://msdn.microsoft.com/en-us/library/w070t6ka.aspx

Store the login information in an encrypted app config file. Under the service project create an app.config file. In that file add a section for the sensitive information (in plain text initially), and once saved ecnrypt the file.

Details here: http://msdn.microsoft.com/en-us/library/89211k9b(v=VS.100).aspx and a walk-through here: http://weblogs.asp.net/jgalloway/archive/2008/04/13/encrypting-passwords-in-a-net-app-config-file.aspx