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.
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.
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âmetro | Valor padrão | Descrição |
|---|---|---|
| Max Pool Size | 100 | O 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 Size | 0 | O 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". |
| PoolBlockingPeriod | Auto | Define 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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 |
SELECT original_login_name, [host_name], client_interface_name, COUNT(*) FROM sys.dm_exec_sessions GROUP BY original_login_name, [host_name], client_interface_name ORDER BY 1, 2, 3 SELECT B.login_name, B.[host_name], B.[program_name], DB_NAME(B.database_id) AS [database], COUNT(*) AS connections FROM sys.dm_exec_connections A LEFT JOIN sys.dm_exec_sessions B ON A.session_id = B.session_id GROUP BY B.login_name, B.[host_name], B.[program_name], DB_NAME(B.database_id) -- Apenas SQL Server 2014 em diante SELECT DB_NAME(A.database_id) AS [database], A.is_user_process, COUNT(A.session_id) AS connections FROM sys.dm_exec_sessions A GROUP BY DB_NAME(A.database_id), A.is_user_process ORDER BY 1, 2 SELECT * FROM sys.dm_os_performance_counters A WHERE A.counter_name = 'User Connections' |
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:
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:
|
1 2 3 4 5 6 7 8 9 10 |
USE [master] GO EXEC sp_configure 'show advanced options', 1; GO RECONFIGURE; GO EXEC sp_configure 'user connections', 5000; -- Alterando para 5.000 conexões no máximo GO RECONFIGURE; GO |
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.
And that's it, folks!
Espero que tenham gostado desse post e até mais!
Portuguese 
English
Legal.
Já tive um problema em um cliente também que estourou o limite de conexões. Criei um alerta lá para monitorar esse número de conexões e me avisar antes de estourar novamente.
Segue o caso:
https://www.fabriciolima.net/blog/2016/06/27/casos-do-dia-a-dia-sql-server-alcancou-o-limite-de-32767-conexoes-em-producao/
Show! Obrigado pelo feedback e por compartilhar mais uma solução para este problema, Fabricio!