Hello people,
Good afternoon.
In this post I will comment on a problem that I recently encountered in a company, in which no application was able to connect to the production database, presenting the message "Login failed for user 'user'."
Introduction and problem description
In the enterprise where this connection error occurred, there are several applications that connect to the production SQL Server database using the same user with SQL Server authentication. Given this scenario, it is complicated to identify by the database to which application a session is associated, or even, which AD user logged in the application and performing those actions in the database.
In order for this identification to be possible, an experienced analyst developed a change in C # applications, so that he informed the user name logged in AD and the system name in the “Program Name” parameter, in the database connection string , getting something like this:
Data Source=myServer; Initial Catalog=myDB; User Id=myUsername; Password=myPassword; Application Name=UsuarioAD/Sistema;
We did the tests and everything worked very well. The application was sending the user / system and through WhoIsActive it was possible to clearly identify the user and system responsible for each session in the production database through the program_name column.
A few minutes after the rise of this change in production, several calls and alerts began to arrive informing the error “Login failed for user 'user'.”, Where the hostnames of origin were the production IIS servers.
The first step in trying to identify what was going on was to analyze the database. We validated that the password was correct, and the login was successful, using the user of the application, because until then, was suspected of changing the password.
It has been checked in the SQL Server logs (Management> SQL Server logs) and there was no incorrect password login failure log. I confirmed in the server settings that Login auditing for login failures was actually enabled (figure below) and really, there was no incorrect password registration on the production servers.
After that, we confirmed that the connection string, which was changed, was actually pointing to the production server. Meanwhile, the error messages were accumulating and although this was happening, several users were managing to use the system normally, connecting to the database normally. We did some tests on the system screen and the error was intermittent: Now it worked, now it had a “Login failed” error.
It was decided to restart the IIS servers, which solved the problem for a few minutes, but soon reoccurred some minutes later.
That was when they raised the point of the SQL Server connection pool. According to Microsoft documentation (https://msdn.microsoft.com/en-us/library/8xx3tyca(v=vs.110).aspx), a pool of 100 connections (default value) is created for each single connection string, that is, for each user / system combination, SQL Server was reserving 100 connections!
Due to this change in the connection string, the bank connection limit was eventually reached and causing the problem mentioned in the post.
For those unfamiliar, connection pooling is a very useful feature as it considerably reduces the overhead generated by applications due to opening / closing connections, since connection pooling keeps connections to the bank always open (during a period), even if inactive due to lack of activity, and manages the opening / closing of database connections.
Connection Pool related parameters in connection string
| Parameter | Standard value | Description |
|---|---|---|
| Max Pool Size | 100 | The maximum number of connections allowed in the pool. Valid values are greater than or equal to 1. Values that are less than Min Pool Size generate an error. |
| Min Pool Size | 0 | The minimum number of connections allowed in the pool. Valid values are greater than or equal to 0. Zero (0) in this field means no minimum connection is initially opened. Values greater than Max Pool Size generate an error. |
| Pooling | 'true' | When the value of this key is set to true, any newly created connections will be added to the pool when closed by the application. On a next attempt to open the same connection, this connection will be drawn from the pool. Connections are considered equal if they have the same connection string. Different connections have different connection chains. The value of this key can be "true", "false", "yes" or "no". |
| PoolBlockingPeriod | Car | Defines the lock period behavior for a connection pool. More information accessing this link. |
Connection Pool Tests
If this happens to you, or any developer has the same idea, you now know the impacts of changing the connection string and how to solve it. In this case, as a connection pool would be opened for each user / system, a smaller pool of 4 connections could be used, for example, but would have many open pools in the bank and the need to keep opening and closing connections would still exist, making connection pooling did not make much sense in this mode of operation.
After solving the problem, the development team decided to prove this theory by creating a small program that simply opened 100 connections using a normal connection string, with the Application Name fixed and the Max Pool Size parameter set to the 20 value. After execution, only 20 connections were opened in the database.
By changing the program to generate random values for the Application Name parameter, SQL Server actually reserved 20 connections for each of the 100 connections made.
How to identify the number of SQL Server instance connections
To perform these instance connection number checks, you can use one of the following queries:
|
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' |
How to identify the maximum number of SQL Server instance connections
To identify the maximum limit of users configured on the instance (SQL Server maximum limit is 32.767 connections), you can use one of the following commands:
How to change the maximum number of SQL Server instance connections
To change the maximum number of SQL Server connections, you can use the command below:
|
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 |
The user connections option specifies the maximum number of concurrent user connections allowed on an instance of SQL Server. The actual number of user connections allowed also depends on the version of SQL Server you are using and the limits of your application or applications and hardware. SQL Server allows a maximum of 32.767 user connections.
Because user connections is a dynamic (self-configuring) option, SQL Server automatically adjusts the maximum number of user connections as needed to the maximum allowable value. For example, if only 10 users are logged in, 10 user connection objects will be allocated. In most cases, you do not need to change the value of this option. The default is 0, which means allowed maximum user connections (32,767) are allowed.
That's it folks!
I hope you enjoyed this post and even more!
Nice.
I've had a problem with a client too that has broken the connection limit. I created an alert there to monitor this number of connections and warn me before bursting again.
Follow the case:
https://www.fabriciolima.net/blog/2016/06/27/casos-do-dia-a-dia-sql-server-alcancou-o-limite-de-32767-conexoes-em-producao/
Show! Thanks for the feedback and for sharing one more solution to this issue, Fabricio!