CODEWITHSUNDEEP
c#sqlsql-serverwcf
jay
jay

Reputation: 131

can't connect to SQL Server from WCF service using account

I am trying to connect to sql server from wcf service. Using integrated security, it works fine but when I use an account, it fails. I am able to connect to sqL server management studio using this account. I am not sure what I am missing, do I need another provider to do the access. appreciate any feedback. 

<add name="GISDBConnectionString" connectionString="Data Source=SVRName;Initial Catalog=db;User ID=GLOBAL\Test;Password=temp"
providerName="System.Data.SqlClient" />

Upvotes: 0

Views: 2042

Answers (2)

Markus
Markus

Reputation: 22456

Based upon the backslash in your account name, I suspect that you are trying to use a windows account in the connection string. There are two kinds of logins that can connect to an SQL Server:

  • Windows Logins that are managed in Active Directory and authorized to access a SQL Server instance.
  • SQL Server authenticated logins that are managed and authorized in SQL Server management studio.

In a connection string, you can opt to use Windows Authentication (Integrated Security=SSPI, you can also use true instead of SSPI). This means that the application will use the account that the process is running under to connect to the SQL Server. The big advantage of this authentication method is that you do not have to store credentials in the connection string. On the other hand, the only way to change the account that is used is to have the application run under another account (or impersonate in the application each time you connect to SQL Server). So there is no way to set another Windows account in the connection string.

If you want to use a SQL Server authenticated account, you have to add the credentials in the connection string (User Id=MyUserName;Password=MyPassword). You can only use an account that has been created at and granted access to the SQL Server instance you try to connect to. By default, a SQL Server instance is configured to only allow Windows authentication, so you might need to enable SQL Authentication first.

Please note that SQL Server authenticated logins cannot contain a backslash - that's the reason for my assumption that you want to use the credentials of a Windows account in the connection string as the username contains a backslash.

So how can you solve the issue:

  • If you want to use the Windows account, let your application run under this account. As it is a WCF application, you'd have to change the application pool account if you host it in IIS. Set your connection string to: Data Source=SVRName;Initial Catalog=db;Integrated Security=SSPI.
  • If you do not want to change the account that the application runs under, create a SQL Server authenticated login at the SQL server and grant it access to the relevant databases. Set the credentials in the connection string: Data Source=SVRName;Initial Catalog=db;User ID=SQLAuthLogin;Password=SQLAuthPwd.

Upvotes: 1

Joao Santos
Joao Santos

Reputation: 21

Try adding Integrated Security=false to your connection string:

<add name="GISDBConnectionString" connectionString="Data Source=SVRName;Initial       Catalog=db;Integrated Security=False;User ID=GLOBAL\Test;Password=temp"
 providerName="System.Data.SqlClient" />

Upvotes: 0

Related Questions