SQL Security with a WCF Service

Question

I've been developing a WCF service that makes calls to a web service and then to the sql server. Everything works when testing from within the same solution. Now, I have hosted the WCF services on IIS and I am trying to make a service call from an asp.net web application. The IIS service is hosted on my local machine for now. I use Windows Authentication when connecting in SQL Server Management Studio. When I make the call to the web service, I'm getting the following error when making the call to SQL, so I'm assuming it doesn't like the credentials:

Login failed for user 'MYDOMAIN\MYMACHINENAME$'.

Here's the code where I call the web service. As you can see, I've tried passing in my credentials explicitly:

var providerService = new ProviderServices.ProviderService();
providerService.Credentials = new NetworkCredential("myusername", "mypassword", "mydomain");
providerService.UseDefaultCredentials = false;

providerService.CheckProviderExclusion(PPLUser.CurrentProgram.ProgramLabel, ps.No, ps.ProviderName, ps.SocialSecurityNo);

Here is the inner text of the exception:

Login failed for user 'PCGUS\BON-0010882$'. at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action'1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover) at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout) at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData) at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource'1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource'1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource'1 retry, DbConnectionOptions userOptions) at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource'1 retry) at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource'1 retry) at System.Data.SqlClient.SqlConnection.Open() at System.Data.Linq.SqlClient.SqlConnectionManager.UseConnection(IConnectionUser user) at System.Data.Linq.SqlClient.SqlProvider.get_IsSqlCe() at System.Data.Linq.SqlClient.SqlProvider.InitializeProviderMode() at System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute(Expression query) at System.Data.Linq.DataQuery'1.System.Linq.IQueryProvider.Execute[S](Expression expression) at System.Linq.Queryable.SingleOrDefault[TSource](IQueryable'1 source) at ProviderExclusionChecker.DataAccess.PCG_Proxy.CheckProviderExclusion(String programName, String providerID, String providerName, String providerSSN) in c:\TFS\PPL_Services\ProviderExclusionChecker\ProviderExclusionChecker\PCG Proxy.cs:line 72 at ProviderExclusionChecker.ServicesApplication.ProviderService.CheckProviderExclusion(String programName, String providerID, String providerName, String providerSSN) in c:\TFS\PPL_Services\ProviderExclusionChecker\ProviderExclusionChecker.ServicesApplication\ProviderService.svc.cs:line 21 at SyncInvokeCheckProviderExclusion(Object , Object[] , Object[] ) at System.ServiceModel.Dispatcher.SyncMethodInvoker.Invoke(Object instance, Object[] inputs, Object[]& outputs) at System.ServiceModel.Dispatcher.DispatchOperationRuntime.InvokeBegin(MessageRpc& rpc) at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage5(MessageRpc& rpc) at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage31(MessageRpc& rpc) at System.ServiceModel.Dispatcher.MessageRpc.Process(Boolean isOperationContextSet)System.Data.SqlClient.SqlException

How can I configure the service to authenticate my sql server connection?

1) Provide a login for MYDOMAIN\MYMACHINENAME$ on SQL Server, 2) run the app pool under an account that has permission to login to SQL Server, or 3) create an account on SQL server for your service and use that account and password in the connection string. — Tim
I would go with #3 and create a SQL account for that service, and give it only the permissions it needs for the WCF service. — Tim
The thing is, my credentials for my machine are the same for sql server (windows active directory credentials). I didn't check out the app pool yet - that may be set to something else. I'll try that. — Jesse Roper

Shiraz Bhaiji Shiraz Bhaiji · Accepted Answer · 2013-09-19T18:08:11

There are several layers here each can choose which authentication it sends done to the next layer.

Your WCF service is calling a web service with windows credentials. Assume this works:

The call comes to IIS, depending on the IIS settings these credentials are used or lost. For example if IIS is anonymous only it is lost.
Then it hits the application pool, where the settings in web.config determine if the credentials are used.
Then it hits the database, where the connection string determines which credentials are used.

There is also a "double hop" problem where a service by default cannot pass a credential that is repassed before it is used. Not sure if this is affecting you.

SQL Security with a WCF Service

2 Answers