SQL Security with a WCF Service

2018-06-20 04:04:06

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 'MYDOMAINMYMACHINENAME$'.

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 'PCGUSBON-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.SqlIntern alConnectionTds.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 owningConnec tion, 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.IQue ryProvider.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:TFSPPL_ServicesProviderExclusionCheckerProviderExclusionCheckerPCG Proxy.cs:line 72 at ProviderExclusionChecker.ServicesApplication.ProviderService.CheckProviderExclusion(String programName, String providerID, String providerName, String providerSSN) in c:TFSPPL_ServicesProviderExclusionCheckerProviderExclusionChecker.ServicesApplicationProviderService.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(Messa geRpc& 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?

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.

I may be late, but I was looking for same resolution. However, I found myself. You can try: 1. Run inetmgr. 2. Go to Application Pools 3. Search for the apppool for which your application is pointing to. 4. Check for identity column 5. You can modify the Identity column value by right clicking properties. 6. Make sure that identity user should have windows authentication.

链接地址: http://www.djcxy.com/p/56732.html

上一篇: 你如何规范Bash中的文件路径？

下一篇: 带有WCF服务的SQL安全性