0

I have two desktop clients with VS 2017. I inherited a C# .NET app that connects to a SQL Server in the next room. We generally use SQL Server authentication (as opposed to Windows authentication) when we perform SQL queries.

I use a connection string that looks something like this ...

"Data Source=DR101; User Id = DR_DATA; Password = CD7504st; Integrated Security=SSPI; TransparentNetworkIPResolution=False"

... which works like a charm from the first desktop. From the second desktop it always fails to access the database, giving an error indicating a Windows login failure ...

... threw an exception.
System.Data.SqlClient.SqlException: Login failed for user 'SDAR\SdarAutoSvc'

I have compared everything I can think of between these two systems. As far as I can ascertain, they are identical in most every way ... both use the same Visual Studio (Help > About, and compared the long list of module versions. Identical on both systems), the same System.Data.dll module, the same App.config, C# source code, input data, etc.

The failing system only fails via this visual studio app - i.e., I can reach the database server from that desktop via SMSS, using the SQL Server credentials (not Windows authentication).

I am out of ideas as to how this second machine might be unable to access the database, and why it apparently uses Windows auth rather than the sql userId/password from the connection string.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Paperclip Bob
  • 346
  • 2
  • 8
  • 4
    Integrated Security=SSPI uses windows credentials. Which requires User have an account on both local and remote machine. Normally I setup a Group Account (or Group Policy) and add users to the Group. Then have the Group Account setup on both local and remote machine (SQL Server PC). Then add the Group Account to the Database Credentials. I find the windows group account is easier than to add each user to the SQL Database Credentials. Usually a large company uses Group Policy and the Group Already exists. So just adding the Group to the database gives all users access to the database. – jdweng May 05 '20 at 04:11
  • 3
    I think you'll find that the user on the first desktop is _actually_ connecting with windows auth without you realising it. I'm not sure why the Visual Studio version matters here. Normally you build the app in Visual Studio and then deploy it to run standalone. You don't install Visual Studio on every machine that needs to run it – Nick.Mc May 05 '20 at 04:19

1 Answers1

3

If you want SQL server Authentication then remove Integrated Security=SSPI; from the connection string.

Try with this connection string -

 "Data Source=DR101; User Id = DR_DATA; Password = CD7504st; TransparentNetworkIPResolution=False"
Mukesh Arora
  • 1,763
  • 2
  • 8
  • 19