Recently my company released a new SQL standards document. Within this new standards document, I found the following line:
Windows integrated security is preferred as far as possible
Hence, im my new wcf web service I have the following connection string (taken directly from web-app where used successfully):
<add name="ProcurementAdministrationPortalEntities"
connectionString="metadata=res://*/ProcurementAdministrationPortalModel.csdl|res://*/ProcurementAdministrationPortalModel.ssdl|res://*/ProcurementAdministrationPortalModel.msl;provider=System.Data.SqlClient;provider connection string="data source=database-server;initial catalog=ProcurementAdministrationPortal_v2;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework""
providerName="System.Data.EntityClient"/>
Now, when trying to use the service, I get the following error:
Exception: The underlying provider failed on Open.
InnerException: Login failed for user 'DOMAIN\D10604B77177C$'.
It's saying that the login failed for the domain/computer name. Does this mean that the machine where the web service is running on needs to be added to the permissions of the database? I am currently logged into AD on said machine with an account that is added to in the database. The account being 'DOMAIN\ANEL5'.
When the machine name is passed as the user ID it usually means that your application pool is running as System. With Integrated Security = true, it tries to login with the computer object ID. Change the app pool to use a domain user ID (we have an OU just for service accounts) and give that domain ID the required SQL permissions.