Login Failed Error when connecting to SQL Server

0

I am unable to connect to one of the database on our work server. Able to connect to one db but not the other (both hosted on the same server). Connecting through C# .Net Framework Application. Here is the setup.

  • Server name: MYSQLSERVER
  • Database 1: mydb1
  • Database 2: mydb2
//connection string for mydb1: 

string connectionString1 = "Data Source=MYSQLSERVER;Initial Catalog=mydb1;Integrated Security=SSPI" //(able to connect to mydb1)

//connection string for mydb2: 

string connectionString2 = "Data Source=MYSQLSERVER;Initial Catalog=mydb2;Integrated Security=SSPI" //(unable to connect to mydb2)

//Connect and open mydb1:

    using (SqlConnection connection = new SqlConnection(connectionString1))
    { 
        SqlCommand command = new SqlCommand("SELECT TOP 10 * FROM sometable", connection);
        command.Connection.Open();  //Success
    }

//Connect and open mydb2:

    using (SqlConnection connection = new SqlConnection(connectionString2))
    { 
        SqlCommand command = new SqlCommand("SELECT TOP 10 * FROM sometable", connection);
        command.Connection.Open();  //Failure
    }

For myDb2 error log has this:

System.Data.SqlClient.SqlException (0x80131904): Cannot open database "mydb2" requested by the login. The login failed.
Login failed for user 'user'

Error Number:4060,State:1,Class:11

My guess is that it's not the code that's the issue but rather some kind of setting in SQL Server for mydb2 (because I am able to connect to mydb1 with exact same connecting string settings).

I checked 'Connection Properties' in SQL Server Management Studio (right click db > Properties > View Connection Properties) and Under Authentication > Authentication Method both have 'Windows Authentication' and Username is set to my username. Also every other setting under this connection properties is exactly the same between mydb1 and mydb2.

I am a beginner when it comes to SQL Server especially playing around with Authentication setting.

Any guidance is appreciated.

c#
.net
sql-server
authentication
ssms
asked on Stack Overflow Aug 5, 2020 by coderguy • edited Aug 5, 2020 by Çöđěxěŕ

1 Answer

0

Your issue isn't your code. You need to check your database login security. Open Microsoft Server Management Studio, and try to log in to your database server with the same credentials you use to connect your database within your application. Make sure you have your server selected, and Authentication is SQL Server Authentication (or how you normally connect to your server). If it logs you in, then your username/password you're using to connect to within your application is wrong. Check your connection string variables. If it doesn't log in, then you will need to create a new user with privilege's for access to the second database "mydb2". Just for reference.. Each created db within a server needs its own user privilege's.

enter image description here

answered on Stack Overflow Aug 5, 2020 by SkiSharp

User contributions licensed under CC BY-SA 3.0