Issue
I try to access Sql Server through Spring boot application. Everything working fine with Sql management studio but when I try to connect with Spring boot application, it's throwing error:
com.microsoft.sqlserver.jdbc.SQLServerException:
Cannot open database <database> requested by the login. The login failed.
If I remove database name from connection string, connection is established but failed if I given database name in connection string.
Solution
Considering that you state that the connection works in you omit the database name (which means the Login will connect to its default database or master) but dosen't when you specific the database, this very likely means that the login doesn't have a user mapped to it in the target database. Firstly you'll need to create a user in the database (you'll need to replace the text in braces ({}):
USE {Target Database};
CREATE USER {Login Name} FOR LOGIN {Login Name};
You don't need to use the Login Name for the User's name (you could have, rather foolishly have USER [Steve] FOR LOGIN [Jayne]), however, it's pretty common that they are the same as it's far more apparent which users and logins are mapped to each other.
This will create a mapped user, however, they won't have any permissions on that database. Let's assume, however, that your application only needs read access to the database, on any and all objects, and nothing more. You would then add the new suer to the db_datareader role:
ALTER ROLE db_datareader ADD MEMBER {User Name};
Of course, you may need to give your User more specific permissions, but that should get you on the right path.
Answered By - Larnu
Answer Checked By - Dawn Plyler (JavaFixing Volunteer)
