Unable to connect to Azure SQL with a login/user

Question

Unable to connect to Azure SQL with a login/user

Michael Payne 121

I am trying to create a readonly user and login for Azure SQL DB and have followed the instructions below:

https://azure.microsoft.com/en-us/blog/adding-users-to-your-sql-azure-database/

I create the login in the master database, and then create the user in my stats database.

In master Database:
CREATE LOGIN readonlylogin WITH password='1231!#ASDF!a';

In Stats Database:

CREATE USER readonlyuser FROM LOGIN readonlylogin WITH DEFAULT_SCHEMA = CGReport;

I then execute the sp_addrolemember stored procedure:

EXEC sp_addrolemember 'db_datareader', 'readonlyuser';

This works but when I try and connect using SSMS I get the following:

It appears as if the user is trying to connect to the master database and not my Stats database, however in Azure SQL DB there is no option to specify a default database.

How can I get this to work? We need a secure readonly login as the database will be connected to a website.

Dan Guzman 9,401 Reputation points

2020-10-26T11:09:55.627+00:00

When you connect from SSMS, click on the Options button and specify Stats as the "Connect to database" name instead of default. Alternatively, you can authenticate at the database level by running this in the Stats database:
CREATE USER readonlyuser WITH password='1231!#ASDF!a';
ALTER ROLE db_datareader ADD MEMBER readonlyuser;
Michael Payne 121 Reputation points

2020-10-26T12:51:18.423+00:00

This is what I have already done and it doesn't work

CREATE LOGIN readonlylogin WITH password='1231!#ASDF!a';

CREATE USER readonlyuser FROM LOGIN readonlylogin
WITH DEFAULT_SCHEMA = CGReport;

EXEC sp_addrolemember 'db_datareader', 'readonlyuser'

I run this script but it still won't let me login with readonlyuser/1231!#ASDF!a or readonlylogin /1231!#ASDF!a

Accepted answer

1 additional answer

Your answer

Dan Guzman 9,401 Reputation points

2020-10-26T11:09:55.627+00:00

When you connect from SSMS, click on the Options button and specify Stats as the "Connect to database" name instead of default. Alternatively, you can authenticate at the database level by running this in the Stats database:
CREATE USER readonlyuser WITH password='1231!#ASDF!a';
ALTER ROLE db_datareader ADD MEMBER readonlyuser;
Michael Payne 121 Reputation points

2020-10-26T12:51:18.423+00:00

This is what I have already done and it doesn't work

CREATE LOGIN readonlylogin WITH password='1231!#ASDF!a';

CREATE USER readonlyuser FROM LOGIN readonlylogin
WITH DEFAULT_SCHEMA = CGReport;

EXEC sp_addrolemember 'db_datareader', 'readonlyuser'

I run this script but it still won't let me login with readonlyuser/1231!#ASDF!a or readonlylogin /1231!#ASDF!a

Answer 1

Michael Payne 121

In the Master Database:

CREATE LOGIN readonlylogin WITH password='1231!#ASDF!a';
CREATE USER readonlyuser FROM LOGIN readonlylogin WITH DEFAULT_SCHEMA = CGReport;

In Stats Database:

CREATE USER readonlyuser FROM LOGIN readonlylogin WITH DEFAULT_SCHEMA = CGReport;

EXEC sp_addrolemember 'db_datareader', 'readonlyuser';

From SSMS enter readonlylogin /1231!#ASDF!a as the credentials to my server, then switch to the advanced tab and enter then database name in the connect to database.

Answer 2

Anurag Sharma 17,631

Hi @Michael Payne , welcome to Microsoft Q&A forum.

The reason for getting this error when trying to connect through SSMS is, you have created the user in 'Stats' Database and not in Master database and by default when you login using SSMS, it connect to master database. there are 2 ways to resolve this.

1). Provide the default database in SSMS as shown in below screenshot:

2). Create the user in Master database as well and you should be able to connect.

Please let me know if this helps.

----------

If this resolves your query, please select 'Accept Answer' as it could help other community members looking for similar queries.

Michael Payne 121 Reputation points

2020-10-26T12:49:58.663+00:00

Hi @AmanpreetSingh-MSFT

I have created the readonlyuser in the Stats Database not the master database.

If I enter by username and password and then go to the advances section as shown in your screenshot, it won't allow me to select the stats database it just throws the error in my screenshot.
Anurag Sharma 17,631 Reputation points

2020-10-26T12:55:16.37+00:00

Hi @Michael Payne , thanks for reverting back.

You can try typing the name of database in 'Connect to database' once and it should work. Please let me know if still an issue.
Michael Payne 121 Reputation points

2020-10-26T13:22:24.447+00:00

It appears I needed to create the User in both the master and the stats database and the type the name of the database as you have described. I am now able to login with the user and have readonly access.
Thanks

Share via

Unable to connect to Azure SQL with a login/user

1 additional answer

Your answer