0

I have an azure sql server named "myCloudServer" and I created 2 databases in it, DB1 and DB2. I am an admin so I used the server admin "pt_admin" to create and manage those 2 databases. I wanted to give a 'read-access' to another person where he can login and do all kind of "Select" queries.

I connected to the master database and executed the following commands:

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

CREATE USER readonlyuser FROM LOGIN readonlylogin;

Then I went into DB1 and DB2 and executed the following commands for each database:

CREATE USER readonlyuser FROM LOGIN readonlylogin;

EXEC sp_addrolemember 'db_datareader', 'readonlyuser';

All the commands were successfully executed by "pt_admin" user. Afterward, I tried to connect (SQL Auth) using the following credentials:

username: readonlyuser 
password: 1231!#ASDF!a

and it is failing for some reason with a message 

cannot connect to "myCloudServer" and that the login failed for user "readonlyuser". Error Code 18456

Note that I made sure I had my IP_Address added to the firewall in azure.

Any idea how to debug or fix this issue?

Ray
  • 781
  • 2
  • 17
  • 42
  • Did you run through the troubleshooter? In your case, error 18456, fourth option here: https://support.microsoft.com/en-gb/help/10085/troubleshooting-connectivity-issues-with-microsoft-azure-sql-database – huysmania May 15 '18 at 05:06
  • 2
    You should be using the login name to connect to the server. UserName is relevant at the database level. – Amit Sukralia May 15 '18 at 05:20
  • You're right! it was a stupid mistake – Ray May 15 '18 at 11:09

1 Answers1

1

Error Code 18456

Error code 18456 means that login failed for user

As Amit Sukralia mentioned that in your case readonlyuser is related to database level, you can't use that to connect the server.

You could use the readonlylogin to connect the server.

You also could use SQL Server Management Studio (SSMS) to check that.

enter image description here

Tom Sun - MSFT
  • 24,161
  • 3
  • 30
  • 47