Here's the error message that is stumping me:
My Web App seems to have the correct connection string. This is exactly what Azure provides me when I click Show Connection String:
Server=tcp:myservertest.database.windows.net,1433;Initial Catalog=MyDatabaseTest;Persist Security Info=False;User ID={your_username};Password={your_password};MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;
Here's the connection string that I put in the Visual Studio Publish Settings Default Connection setting:
Server=tcp:myservertest.database.windows.net,1433;Initial Catalog=MyDatabaseTest;Persist Security Info=False;User ID=*****;Password=***********;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;
I have confirmed the username and password are correct...I used the same ones to connect via Microsoft SQL Server Management Studio.
I tried setting up the Diagnostic settings as follows, but I'm not exactly sure where I can find the resulting logs. I don't see them in Kudo services, but I believe they'd be accessed elsewhere.
Does anyone know where they're stored?
Also, what other ways could this Login failed for user error message be debugged?
Locate the user that was failing to log in. A x on the user indicates this user has login disabled. To allow the user to login, right-click the user and choose Properties, then click the Status page. Enabling login for the user and click OK.
Right click on the database server and go to properties. Choose the security option and check "SQL Server and Windows authentication mode". Enable TCP/IP connection in SQL Configuration Manager. Restart your SQL server.
Use the Azure portal. Open the relevant database. At the top of the database's Auditing page, click View audit logs. Audit records opens, from which you'll be able to view the logs.
The error looks like you are able to connect to the server but the server rejects the login. Debugging in the server logs would help, so you are looking at the right place.
You can enable Azure SQL Database Auditing & Threat Detection. You can enable it on SQL Server instance level by opening your SQL Server resource and selecting Security / Auditing & Threat Detection. Select a storage account to store logs in Storage details (see picture below). For more information, see https://docs.microsoft.com/en-us/azure/sql-database/sql-database-auditing.
After enabling auditing try to login to your database. After that you can find the logs in the specified Azure Storage Account in blob container named sqldbauditlogs. The logs are in folder /servername/databasename/SqlDbAuditing_ServerAudit_NoRetention/yyyy-mm-dd/ in files with xel extension. You can download and open the .xel -file in SSMS (File / Open / File…). The xel file contains events and you can see login attempts there.
Event field succeeded tells if the login failed or not, and field server_principal_name contains the username in both cases. From text in field additional_information you can find error_code (in the xml). Error code 40615 is blocked by firewall and code 18456 is wrong username or password. (error codes from https://docs.microsoft.com/en-us/azure/sql-database/sql-database-develop-error-messages)
You can also find some information in the database system tables for analysing the connections, e.g. sys.event_log (see: https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-event-log-azure-sql-database?view=azuresqldb-current).
More information on troubleshooting the Azure SQL Database connectivity can be found here: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-troubleshoot-common-connection-issues.
I hope this helps you forward with debugging the connection.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With