TransWikia.com

Can't connect to Azure SQL with error 18456

Database Administrators Asked by user2637453 on December 15, 2021

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?

2 Answers

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!

Answered by user2637453 on December 15, 2021

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.

Answered by Josh Darnell on December 15, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP