WCF Nhibernate Service with Integrated Security=True error

Question

Hello there,

I am working on a WCF Service(implemented with Fluent NH), and it is hosted as a Windows Service.

I am using a console application to test calling service methods.

Now, when I set connection string in hibernate.cfg.xml as

<property name="connection.connection_string">Server=dev;Initial Catalog=DBTest;Integrated Security=True;</property>

It throws the error: SqlException: Login failed for user

But if I change connection string to:

<property name="connection.connection_string">Server=dev;Initial Catalog=DBTest;User Id=twr;Password=manager1;</property>

It successfully calls the service methods.

While all WCF Service Library, Hosting Windows Service and Console Application sits on my local machine only,

Can anyone please help me in understanding why it doesn't work with Integrated Security=true?

Thank you!

Answer 1

Integrated Security = true means that this application will attempt to authenticate itself using "Windows Authentication" mode on the SQL Server you're connecting to. The service is running under a particular user account - it will use that user's account permissions to login to SQL.

If your SQL Server instance does not permit the user account of that Windows Service to login, you'll get that error. It's quite likely that the user the service runs as in your unit test is different than the test console application.

Try this - run your test console application and bring up Task Manager. Under the processes tab, look at the process associated with your service and the username it is running under. Run your unit test framework and note the username as well - they're probably different.

Go to SQL Server Management Studio and check to see under Security -> Logins whether the account that the service runs as (the one you found by using Task Manager) is listed. If not, add it and grant it permissions.