Clique no banner para conhecer e adquirir o meu treinamento de Bancos de Dados no Azure

Erro de login failed for user ‘usuario’ ao tentar conectar no SQL Server por uma aplicação .NET (C#)

Visualizações: 4.711 views
Tempo de Leitura: 5 minutos

Olá pessoal,
Boa tarde.

Neste post vou comentar sobre um problema que encontrei recentemente em uma empresa, no qual nenhuma aplicação estava conseguindo se conectar ao banco de dados de produção, apresentando a mensagem de “Login failed for user ‘usuario’.”

Introdução e descrição do problema

Na empresa onde esse erro de conexão ocorreu, existem diversas aplicações que se conectam à base de dados SQL Server produção, utilizando um mesmo usuário, com autenticação SQL Server. Diante desse cenário, fica complicado identificar pelo banco de dados a qual aplicação uma sessão está associada, ou mesmo, qual o usuário do AD logado na aplicação e realizando aquelas ações na base.

Para que essa identificação fosse possível, um experiente analista desenvolveu uma alteração nas aplicações C#, de forma que ele informasse o nome do usuário logado no AD e o nome do sistema no parâmetro “Program Name”, na string de conexão com o banco de dados, ficando algo como isso:
Data Source=myServer; Initial Catalog=myDB; User Id=myUsername; Password=myPassword; Application Name=UsuarioAD/Sistema;

Fizemos os testes e tudo funcionou muito bem. A aplicação estava enviando o usuário/sistema e pelo WhoIsActive era possível identificar claramente o usuário e o sistema responsáveis por cada sessão no banco de dados de produção, através da coluna program_name.

Alguns minutos após a subida dessa alteração na produção, começaram a chegar vários chamados e alertas informando o erro “Login failed for user ‘usuario’.”, onde os hostnames de origem eram os servidores de IIS de produção.

sql-server-login-failed-for-user

O primeiro passo para tentar identificar o que estava ocorrendo, foi analisar o banco de dados. Validamos se a senha estava correta, e o login foi realizado com sucesso, utilizando o usuário da aplicação, pois até então, estava suspeitando de alteração em senha.

Foi verificado nos logs do SQL Server (Management > SQL Server logs) e não havia nenhum registro de falha de login por senha incorreta. Confirmei nas configurações do servidor que a opção de Login auditing para falhas de login estava realmente habilitada (figura abaixo) e realmente, não havia registro de senha incorreta nos servidores de produção.

sql-server-login-auditing

Após isso, confirmamos que a string de conexão, que foi alterada, realmente estava apontando para o servidor de produção. Enquanto isso, as mensagens de erro iam se acumulando e embora isso estivesse ocorrendo, vários usuários estavam conseguindo utilizar normalmente o sistema, se conectando no banco de dados normalmente. Fizemos alguns testes na tela do sistema e o erro era intermitente: Ora funcionava, ora apresentava erro de “Login failed”.

Optou-se por reiniciar os servidores de IIS, o que resolveu o problema por alguns minutos, mas logo voltou a ocorrer uns 20 minutos depois.

Foi então que levantaram o ponto do pool de conexão do SQL Server. Conforme documentação da Microsoft (https://msdn.microsoft.com/en-us/library/8xx3tyca(v=vs.110).aspx), um pool de 100 conexões (valor padrão) é criado para cada string única de conexão, ou seja, para cada combinação de usuário/sistema, o SQL Server estava reservando 100 conexões!

Por conta essa alteração na string de conexão, o limite de conexões do banco acabou sendo atingido e ocasionando o problema citado no post.

Para quem não conhece, o pool de conexão é um recurso muito útil, pois ele reduz consideravelmente o overhead gerado nas aplicações devido a abertura/fechamento de conexões, uma vez que o pool de conexões mantém as conexões com o banco sempre abertas (durante um determinado período), mesmo que inativas por falta de atividade, e gerencia a abertura/fechamento de conexões com o banco de dados.

Parâmetros relacionados a Pool de conexão na connection string

ParâmetroValor padrãoDescrição
Max Pool Size100O número máximo de conexões permitidas no pool.

Os valores válidos são maiores que ou igual a 1. Valores que são menos de Min Pool Size geram um erro.
Min Pool Size0O número mínimo de conexões permitidas no pool.

Os valores válidos são maiores ou iguais a 0. Zero (0) neste campo significa que nenhuma conexão mínimo inicialmente é aberto.

Valores maiores que Max Pool Size geram um erro.
Pooling'true'Quando o valor dessa chave é definido como verdadeiro, qualquer recém-criado conexão será adicionada ao pool quando fechada pelo aplicativo. Em uma próxima tentativa de abrir a mesma conexão, essa conexão será desenhada do pool.

Conexões são consideradas iguais se tiverem a mesma cadeia de conexão. Conexões diferentes têm diferentes cadeias de conexão.

O valor dessa chave pode ser "true", "false", "yes" ou "no".
PoolBlockingPeriodAutoDefine o comportamento de período de bloqueio para um pool de conexões. Mais informações acessando este link.

Testes no Pool de conexão

Se isso ocorrer com você, ou algum desenvolvedor tiver essa mesma ideia, agora você já sabe os impactos de alteração a connection string e como resolver. Nesse caso, como seria aberto um pool de conexões para cada usuário/sistema, poderia ser utilizado um pool menor, de 4 conexões, por exemplo, mas teriam muitos pools abertos na banco e a necessidade de ficar abrindo e fechando conexão continuaria existindo, fazendo com que o pool de conexões não tivesse muito sentido nesse modo de funcionamento.

Após resolver o problema, a equipe de desenvolvimento decidiu comprovar essa teoria criando um pequeno programa que simplesmente abria 100 conexões utilizando uma string de conexão normal, com o Application Name fixo e o parâmetro Max Pool Size configurado com o valor 20. Após a execução, foram abertas apenas 20 conexões no banco.

Ao alterar o programa para gerar valores aleatórios para o parâmetro Application Name, o SQL Server realmente reservou 20 conexões para cada uma das 100 conexões realizadas.

Como identificar o número de conexões da instância SQL Server

Para realizar essas verificações de número de conexões da instância, você pode utilizar uma das queries abaixo:

Como identificar o número máximo de conexões da instância SQL Server

Para identificar o limite máximo de usuários configurados na instância (o limite máximo do SQL Server é 32.767 conexões), você pode utilizar um dos comandos abaixo:
sql-server-max_connections

sql-server-sys-configurations-user-connections

sql-server-sp_configure-user_connections

Como alterar o número máximo de conexões da instância SQL Server

Para alterar o número máximo de conexões do SQL Server, você pode utilizar o comando abaixo:

A opção user connections especifica o número máximo de conexões de usuário simultâneas permitido em uma instância do SQL Server. O número real de conexões de usuário permitidas depende também da versão do SQL Server que você está usando e dos limites de seu aplicativo ou aplicativos e hardware. SQL Server permite um máximo de 32.767 conexões de usuário.

Como conexões de usuário é uma opção dinâmica (autoconfigurável), o SQL Server ajusta o número máximo de conexões de usuário automaticamente conforme o necessário, até o valor máximo permitido. Por exemplo, se somente 10 usuários estiverem conectados, 10 objetos de conexão de usuário serão alocados. Na maioria dos casos, não é necessário alterar o valor dessa opção. O padrão é 0, o que significa que as permitidas conexões máximas (32,767) de usuário são permitidas.

É isso aí, pessoal!
Espero que tenham gostado desse post e até mais!