CODEWITHSUNDEEP
authenticationazure-sql-database
user70192
user70192

Reputation: 14214

Cannot open database "master" on SQL Azure

TITLE: Connect to Server

Cannot connect to tcp:ohimryXusa.database.windows.net,1433.

ADDITIONAL INFORMATION:

Hello,

I have a SQL Azure database. This database has a username / login that I want to use to access it. When I try to connect to the database by SQL Server Database Management Studio, I receive an error that says:

Cannot open database "master" requested by the login. The login failed.
Login failed for user 'mydbusername'.
This session has been assigned a tracing ID of '00000000-0000-0000-0000-000000000000'.  Provide this tracing ID to customer support when you need assistance. (Microsoft SQL Server, Error: 4060)

I have other logins that I can successfully connect to the database with. I tried executing the following on my database, to ensure there was a user:

CREATE USER mydbusername

I receive an error that says:

Msg 15023, Level 16, State 1, Line 1
User, group, or role 'mydbusername' already exists in the current database

I verified the user existed by logging into the master database. Once there, I ran: SELECT * FROM sys.sql_logins;

I wanted to ensure that 'mydbusername' had access on the database. So I logged in, with a more priveleged account, into my database and ran:

EXEC sp_addrolemember 'db_datareader', 'mydbusername'
EXEC sp_addrolemember 'db_datawriter', 'mydbusername'
EXEC sp_addrolemember 'db_owner', 'mydbusername'

The message said: Command(s) completed successfully.

At this point, we know a) There is a user with the name 'mydbusername'. b) There is a login with the name 'mydbusername'. c) We know that 'mydbusername' has 'db_datareader', 'db_datawriter', and 'db_owner' rights to the database.

I tried logging in via the management screen over the web. I was able to successfully login and execute queries. However, when I try to login via SQL Server Management Studio, I receive the message above. I am using

mydbusername@ohimryXusa for the "Login" field. I've verified that the password is correct. I also verified the Server Name is correct. What am I doing wrong? I really need this because I'm getting the error from my code. Thank you!

Upvotes: 4

Views: 11869

Answers (3)

Srinath
Srinath

Reputation: 31

I know this is old thread, but I might help others who are facing the same problem...I created the user in master database without granting any special permissions - This resolved the problem. Looks like in Azure, all users that belong to user databases should also present in master database??!!

Upvotes: 3

Michael Angstadt
Michael Angstadt

Reputation: 890

Because Azure uses database servers for multiple databases you can't just log into Management Studio (connect to the Object Explorer)

This would give you visibility to everyone's database whose on your same server.

To avoid this, simply close-out of the initial login prompt dialogue you're presented with on start-up and click 'New Query' once it closes.

You'll be prompted to connect - but if you go to connection settings and select your database as the initial catalog you'll be able to script your (and only your) database from there.

No object explorer - but at least you'll be able to directly script your DB.

Upvotes: 1

astaykov
astaykov

Reputation: 30903

login failed is most probably cuased by wrong login/password combination. Please make sure you are using the existing LOGIN, and not the USER while trying to login! Note that when you want to authenticate with SQL Server, you have to use the LOGIN created and not the USER. You have to find out which LOGIN is your "mydbusername" associated with. It is good that you have the user, and that user is added to different roles, but a USER without associate login is nothing.

You may want to refer this documentation.

Upvotes: 5

Related Questions