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.
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.
Check you database name with "Microsoft SQL Server Management Studio" after update your connection string.
The database name was "ETHEM_USER", but I wrote it as "ETHEM-USER".
My fault is "Unexpected error running Liquibase: com.microsoft.sqlserver.jdbc.SQLServerException: Cannot open database "ETHEM-USER" requested by the login. The login failed."
I wanted to share my related issue that this post made me aware of as it helped to resolve my problem. Hopefully, this may avoid some extra hours trying to figure this out.
I was able to get my Tomcat 10.1 JDBC connection to work finally after removing the "databaseName=" in my URL in the jdbc resource in context.xml as mentioned here. My app worked as expected in Eclipse using the same instance of Tomcat and connecting to the same local 2022 SqlServer Express DB. However, as mentioned here, I got the same "SQLServerException: Cannot open database .....The login failed. " error when the war was deployed to my local instance of Tomcat.
Removing the mentioned parameter resolved this issue. While my user did work in Eclipse and also in SSMS, it was a user that had read only access to only 2 tables and nothing else. I suspect the internal of SQLServer did not allow such a restricted user to login via a service.
