August 3, 2023 at 9:05 am
Do repeated login failures into SQL affect performance. Surely some resources are used with each attempted ack & nack? Certainly log writes. I have careless app owners who repeatedly fall foul of my attempts to stop this happening as their apps try access every 10 seconds or so. They failures fill my logs & I would like to be able to fight them with logic about resources.
I don't suppose there's any way to block an attempt at access (that gets denied) within SQL?
These are kosher attempts.
August 3, 2023 at 1:19 pm
This was removed by the editor as SPAM
August 3, 2023 at 2:29 pm
Sure. There's some overhead associated with this. It's probably not massive unless we're talking 10s of thousands of failed attempts. But without a doubt, yeah. Everything the server does, everything, comes with a cost. As to preventing it, not really. If they know the network name or the IP address, they can always try. You can block them further by changing the port number. At least then they won't be able to even make the failed attempt on SQL Server. Windows will still be dealing with the failed attempt though, so still some sacrifice.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 3, 2023 at 3:44 pm
The repeated failures are a denial-of-service attack. That's how those work, as they overload network cards that have to ack or nack each thing. Or they hold open a handle waiting for an ack.
There's nothing that can handle this in SQL Server because once you start seeing this, you still have to respond. How can SQL tell that an app hasn't just sent wrong credentials rather than being a problem?
The way to do this is at the network layer, which is a routing issue. I'd track these from logs, and then open tickets with the network team to add rules and whack the route, blocking at the network. You can do this with the Windows/Linux firewall locally, but then you're managing a network.
August 3, 2023 at 5:15 pm
If they are "kosher" login attempts as you say, then this sounds like users fat-fingering credentials. If this is the case, I would push back on the app writers and tell them that you are switching to Windows authentication and that SQL authentication will be disabled. Windows authentication would remove the "fat finger" problem. That, or push back on the app writers to have them do a connection to the database and validate that it is successful and ONLY attempt to connect once with bad credentials. If it fails, then don't try again every 10 seconds and instead fix the problem or have the app notify someone if it is an automated tool.
If that is not an option, you could set up a different identity provider than SQL for the application and the application could talk to SQL using a known valid authentication account. Basically, instead of the app logging in as the user, the app would log in as a service account and then handover to the user after validating the authentication. This does pose some risks though, so I wouldn't use this as my primary authentication approach and would use this approach very sparingly and only on a system that doesn't hold company critical data. We had to use this approach with one of our systems due to how it was designed and how it was to be used. Essentially, we have a table of username and password hashes that we encrypt and the app connects to the database as a generic user, validates the username and password pair in the table, looks at what permissions the user should have in the app, and sends that back to the application. Due to some limitations in how it was designed and requirements from end users, we couldn't just have the user pass in their credentials and have it log in with their access, so we needed to have this approach. I would NOT recommend this approach for anything company critical though as it has the risk of a user finding a bug or backdoor in the app that would allow them to connect as the generic user. Plus it is a pain in the butt if/when we change the generic users password as it is hard-coded into the app...
A third approach (again, that I do not recommend) is to just turn off login logging completely. That would remove it from the log.
The above approaches do not stop a DOS or DDOS attack, but if they are kosher attempts that are just fat-fingering, the first approach removes the "fat finger" problem, the second and third approach removes the failed logins from the log.
As for performance impact, I know I turned on successful logon logging at one point on one of our systems at the suggestion of an auditor and approval of my manager and I learned really quickly that busy systems that have a lot of activity in a 3rd party app end up with a LOT of logon logging and there was a HUGE system impact. There were thousands of new entries in the log per minute as the app would connect, do 1 small task and disconnect and repeat that 10-100 times per task performed by the end users. Which resulted in the app appearing to crash to end users who would terminate it in the middle of stuff and it created a TON of bad data. Had that turned on for about an hour and had at least 30 support tickets come in before I changed it back. So, like Grant said, if you have a lot of failed logins at a time, there will be an impact. PLUS if you have slow disk for your logs, there will be an impact as each log entry needs to get written to disk. So if you are writing 1 line per failed entry and you get 100 per second (random number), that's 100 writes to disk plus the network overhead.
With Grant's comment about changing the port number, that would work unless you have the SQL Browser service running in which case the app may just be reaching out to the server using the server\instance name method and the port is not provided. If that is the case, then changing ports won't stop the app.
If this is an "internal only" system (ie no access to the internet or external parites), I would strongly encourage you and your developers to switch to windows authentication if possible and if not, get the app developers to build in better error handling on failed SQL connections. If the app is a Linux, Mac, or mobile app, then that makes it a bit more challenging mind you but as far as I know, it should still be possible. The changes don't need to be an overnight thing, BUT knowing developers and DBAs (I do both!), I know that if you don't give deadlines on when it will happen, it won't get done. DBA's should discuss with the developers that the changes are happening (switching to windows authentication), why they are changing (reduces "fat finger" problems with passwords and increases overall system security), when they are changing (2 months from today), and what the developers will need to do (change the connection strings to use windows authentication). Mind you, as a developer, I hate it when I am told "we are doing this thing so make your stuff work". As a DBA, I hate when a developer tells me "that change will take us 6 months to implement once we start working on that feature and we won't have time for at least 1 more year". I've been on both sides of that and it drives me nuts. BUT as a DBA, when I have explained that we are doing XYZ because of ABC and would like to have it done by dd/MM/yyyy, developers are generally more accepting of the change. From the developer side, I am much more receptive to changes if I understand why they are being done.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
August 11, 2023 at 5:47 am
As others mention the correct fix is to get them to stop the failed logins. Depending on the size of your organization that may take awhile. In the meantime something you can do to try and keep your logs to a manageable size are to bump up the maximum number of error log files to 99, and then set up a job to cycle the log every couple of days. Won't fix the problem, but can help keep your logs usable.
Set number of error logs to 99:
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', REG_DWORD, 99
Cycling the error log can be done with:
EXEC sp_cycle_errorlog
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply