Azure SQL Database
An Azure relational database service.
5,596 questions
Azure - Microsoft.Data.SqlClient.SqlException (0x80131904): Login failed for user ''. at Microsoft.Data.ProviderBase.DbConnectionPool.CheckPoolBlockingPeriod(Exception e)
Tiaan Opperman
6
Reputation points
Hi guys
Dont know where to start, we have a SQL server and we recently added a new database which connects to the same SQL as the Production server but will function as our DEV environment.
When trying to run Swagger API calls we get error below:
Microsoft.Data.SqlClient.SqlException (0x80131904): Login failed for user ''. at Microsoft.Data.ProviderBase.DbConnectionPool.CheckPoolBlockingPeriod(Exception e) at Microsoft.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) at Microsoft.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection) at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) at Microsoft.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) at Microsoft.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) at Microsoft.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry, SqlConnectionOverrides overrides) at Microsoft.Data.SqlClient.SqlConnection.Open(SqlConnectionOverrides overrides) at Microsoft.Data.SqlClient.SqlConnection.Open() at DataAccessApi.Repositories.ClientRepository.GetAll() in D:\a\1\s\DataAccessApi\Repositories\ClientRepository.cs:line 33 at lambda_method2(Closure , Object , Object[] ) at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.SyncObjectResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments) at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeActionMethodAsync() at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted) at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeNextActionFilterAsync()--- End of stack trace from previous location --- at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context) at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted) at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeInnerFilterAsync()--- End of stack trace from previous location --- at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Awaited|20_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted) at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope) at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope) at Microsoft.AspNetCore.Routing.EndpointMiddleware.g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger) at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context) at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context) at Swashbuckle.AspNetCore.SwaggerUI.SwaggerUIMiddleware.Invoke(HttpContext httpContext) at Swashbuckle.AspNetCore.Swagger.SwaggerMiddleware.Invoke(HttpContext httpContext, ISwaggerProvider swaggerProvider) at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context)ClientConnectionId:67c74bb9-a4a0-4792-a4b3-db0600b252e1Error Number:18456,State:1,Class:14ClientConnectionId before routing:8f7dce97-86d5-4fe2-9ecb-f4e27f756e31Routing Destination:abac48a4ab02.tr1290.southafricanorth1-a.worker.database.windows.net,11009HEADERS=======Accept: text/plainAccept-Encoding: gzip, deflate, brAccept-Language: enCookie: ARRAffinity=71ef569b0a5a7357d10d594ccee812c9d40fec9981d73638dc39a5a39bc00e89; ARRAffinitySameSite=71ef569b0a5a7357d10d594ccee812c9d40fec9981d73638dc39a5a39bc00e89Host: sitenamehidden-hiddenforsecuritypredictabilityapistaging.azurewebsites.netMax-Forwards: 10Referer: https://URLofapp.azurewebsites.net/swagger/index.htmlUser-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/121.0.0.0 Safari/537.36sec-ch-ua: "Not A(Brand";v="99", "Google Chrome";v="121", "Chromium";v="121"sec-ch-ua-mobile: ?0sec-ch-ua-platform: "Windows"Sec-Fetch-Site: same-originSec-Fetch-Mode: corsSec-Fetch-Dest: emptyX-ARR-LOG-ID: 4d307431-7235-489e-ab75-5dfeba52372cCLIENT-IP: 105.184.164.123:53709DISGUISED-HOST: sitenamehidden-hiddenforsecuritypredictabilityapistaging.azurewebsites.netX-SITE-DEPLOYMENT-ID: sitenamehidden__b771WAS-DEFAULT-HOSTNAME: sitenamehidden-hiddenforsecuritypredictabilityapistaging.azurewebsites.netX-Forwarded-Proto: httpsX-AppService-Proto: httpsX-ARR-SSL: 2048|256|CN=Microsoft Azure TLS Issuing CA 06, O=Microsoft Corporation, C=US|CN=*.azurewebsites.net, O=Microsoft Corporation, L=Redmond, S=WA, C=USX-Forwarded-TlsVersion: 1.2X-Forwarded-For: 105.184.164.123:53709X-Original-URL: /api/ClientX-WAWS-Unencoded-URL: /api/Client
Is this something that I need to add on the SQL permissions or on the database or the storage account?
Thank you in advance, if any light can be shed how to start to troubleshoot this / fix this.
{count} votes
-
Amira Bedhiafi 23,011 Reputation points2024-03-01T14:22:00.4266667+00:00 Check this old thread : https://learn.microsoft.com/en-us/answers/questions/795106/connection-to-sql-database-in-azure
-
Erland Sommarskog 110K Reputation points2024-03-01T22:37:27.53+00:00 What sort of authentication are you using?
You talk about Azure, so it seems like you are talking about Azure SQL database. But then you talk about SQL Server as if it was on-prem or an Azure VM. Can you clarify?
-
Greg Low 1,760 Reputation points Microsoft Regional Director2024-03-02T01:10:09.6266667+00:00 Presuming it's an Azure SQL Server (i.e., a logical server), two options:
#1: If you are connecting using a user that's mapped to a login, you'll need to make sure that same user is created in the Azure SQL Database as well. Use CREATE USER blah FOR LOGIN blah. And the user will need to have permissions set up in the DB as well.
#2: Which address are you connecting to? The server's URL or the DB's URL? If it's the server, make sure you have passed the correct database name in the connection string.
-
Tiaan Opperman 6 Reputation points
2024-03-05T06:18:31.16+00:00 Hi,
apologies for not clarifying.
We use Azure SQL server and Azure Databases linked to that server. Its not a standalone server in Azure, or a on prem SQL server.
We did create that user in the database with the command, but thats the error I got after doing so which was pasted in the ticket details in the beginning.
Hope that helps?
-
Erland Sommarskog 110K Reputation points
2024-03-05T22:15:41.46+00:00 Unfortunately, I'm quite much in the dark. You are using some Swagger API, and you get a login error, and on this point you seem to give up.
What credentials to you provide? What type of authentication do you use?
You say "linked to that server"? What server?
"create that user" - Which user? What sort of user?
"with the command" - What command?
-
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(198.4, 41%, 29%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ELM%3C/text%3E%3C/svg%3E)
Luis Miguel Rocha 0 Reputation points2024-03-18T16:28:31.8133333+00:00 @Erland Sommarskog
I currently have this exact issue and can explain everything that im doing in detail:
I have an asp.net mvc app which uses entity framework to autogenerate the database schema based on the models.
This app is now published in azure as well as the database.
I am currently trying to use github actions to automate: when there is a push to the production branch the yml script builds, runs tests, updates the database (again with EF) and then publishes the app into azure.
I get the error that is showed on the top when trying to update database using:dotnet ef database update -p ./questvault/questvault --connection "Server=tcp:<server>.database.windows.net,1433;Initial Catalog=<db_name>;Encrypt=False;TrustServerCertificate=False;Connection Timeout=60;Authentication=Active Directory Default;Trusted_Connection=True;"and the error:
Microsoft.Data.SqlClient.SqlException (0x80131904): Login failed for user ''.
I've tried multiple things and nothing works, I would love some help if anyone can.
Thank you in advance.
Sign in to comment
Sign in to answer