0

I can log into the db fine from SSMS with 

localhost\MSSQLSERVER01

username: DOMAIN\user.name (Windows Authentication)

But when I try connecting from my application I get

Error Number:18456,State:1,Class:14
Login failed for user 'DOMAIN\User.Name'.

Even though my account can access the db in SSMS and has sysadmin permissions.

Checking the SQL log, I can see this message whenever the login fails:

2019-01-22 09:46:42.13 Logon       Login failed for user 'DOMAIN\User.Name'. Reason: Could not find a login matching the name provided. [CLIENT: <local machine>]
2019-01-22 09:48:33.74 Logon       Error: 18456, Severity: 14, State: 5.

I tried adding NT AUTHORIRTY\NETWORK SERVICE to both /server/security/logins as well as /server/database/security/users but it made no difference

Why is this happening, or how can I possible debug this when the error message gives me basically no information?

The connection string:

Server=localhost; Database=dbname; Trusted_Connection=True;MultipleActiveResultSets=true;

But I also tried

Server=localhost\\MSSQLSERVER01; Database=dbname; Trusted_Connection=True;MultipleActiveResultSets=true;

with the same results

Bassie
  • 9,529
  • 8
  • 68
  • 159

2 Answers2

1

You connection string should be like this:

data source=Server\gaurav.goel;database=DBName;Integrated Security=True;MultipleActiveResultSets=True

Another approach is to use UDL file - create a text file on your machine and change its extension to UDL. Then open this file and try to add your connection into that and check if you can connect or not.

Gaurav
  • 782
  • 5
  • 12
  • I'm still seeing the same error after making this change – Bassie Jan 22 '19 at 10:10
  • ok create a text file on your machine and change its extension to UDL. Then open this file and try to add your connection into that and check if you can connect or not – Gaurav Jan 22 '19 at 10:15
  • Seems there was some confusion with which config file was being called along with a change in environments (meaning my sql instance is called `localhost\mssqlserver01` where it *should* be just `localhost` - sorry about that.. But thank you for the UDL tip that is super useful – Bassie Jan 22 '19 at 10:28
  • Can you please mark it as answer as it helped you :). I have updated my answer. – Gaurav Jan 22 '19 at 10:29
1

An additional thing that might be necessary if it hasn't been done already:

  • Right click on the server in SSMS, select the "Properties" option.

  • Select the "Connections" tab.

  • Check the "Allow remote connections to this server" option.

If you're trying to access it remotely using windows authentication:

  • Right click on the server and click Properties

  • Go to the Security page

  • Under Server authentication choose the SQL Server and Windows Authentication mode radio button

  • Click OK

  • Restart SQL Services

Taken from: https://stackoverflow.com/a/23395581/6391339

Fross
  • 122
  • 13