Azure SQL – Can’t Connect with Error 18456

azure-sql-databasesql serversql-authentication

I'm trying to grant access to my Azure SQL database (on an elastic pool) to an external developer using SQL Authentication.

In the database (not master) I ran the following commands:

create user [UserName] with password='Password', default_schema=[dbo]

alter role db_owner add member [UserName]

He can't access the database, he gets the error:

Login failed for user 'UserName'. (.Net SqlClient Data Provider) Error Number: 18456 Severity: 14 State: 1 Line Number: 65536

Both our IPs have been whitelisted, but only I can login to the database with those credentials. Any idea where things are going wrong?

Best Answer

There are a bunch of reasons their attempt to log in could fail, chief among them being they are just not typing the username or password correctly ?

Check the sys.event_log DMV for a more detailed reason for the login failure. You should be able to filter to connection_failed events:

SELECT *
FROM sys.event_log
WHERE event_type = 'connection_failed'
ORDER BY start_time DESC;

This will let you know if they are having firewall issues (blocked_by_firewall), timeouts (client_close), typos (invalid_login_name), etc.

Related Question