3

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?

user2637453
  • 141
  • 5

2 Answers2

3

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.

Josh Darnell
  • 30,133
  • 5
  • 70
  • 124
1

So i turned out that the developer hadn't specified the database in the client tool he was using (Visual Studio I think)... so sort of a fat finger error!

user2637453
  • 141
  • 5