Microsoft Fail - SQL Server on Linux does not log successful logins
-
So Microsoft has been pushing everyone to Linux on SQL server. They have been blatantly pushing it through constant emails and blog posts. However they dont support a very critical feature, SQL logging successful logins. Failed logins are recorded as expected, but successful logins are not. On Windows you can turn this on, but Linux version of SQL server does not allow this. Microsoft knows about this, but isn't willing to fix it now.
You are still able to perform server audits for this stuff, but you have no way of being alerted through log files if there is a successful login. There are many reasons you would want to alert on successful logins. I would see this as a critical security feature in my mind. Am I wrong to think successful logins are not important?
-
In certain scenarios, I think that successful login records are just as useful as the failed login records.
-
I wouldn't say you're wrong to want this detail recorded, but if your credentials are compromised in such a way that anyone can log in to your database then you've already lost control.
-
@DustinB3403 said in Microsoft Fail - SQL Server on Linux does not log successful logins:
I wouldn't say you're wrong to want this detail recorded, but if your credentials are compromised in such a way that anyone can log in to your database then you've already lost control.
This is more about insider threat IMO
-
@IRJ said in Microsoft Fail - SQL Server on Linux does not log successful logins:
This is more about insider threat IMO
I agree, which still goes to the fact that if your credentials are comp'd, it doesn't matter what other security is in place.
Just like having a root password of "root", doesn't do much good to know that someone from <insert location> logged in. The damage is done.
As a point of "we know this occurred" sure I would love to have those details, but in the grand scheme that's like trying to create a CYA after a breach.
-
Here are some more details and the response received from Microsoft on the issue.
-
@DustinB3403 said in Microsoft Fail - SQL Server on Linux does not log successful logins:
@IRJ said in Microsoft Fail - SQL Server on Linux does not log successful logins:
This is more about insider threat IMO
I agree, which still goes to the fact that if your credentials are comp'd, it doesn't matter what other security is in place.
Just like having a root password of "root", doesn't do much good to know that someone from <insert location> logged in. The damage is done.
As a point of "we know this occurred" sure I would love to have those details, but in the grand scheme that's like trying to create a CYA after a breach.
Successful logins can be helpful because you can attach justification to them when they are occur if they are infrequent. For example connecting to a database at 2am on Saturday with no tickets or issues open a DB would be suspicious as hell.
-
@IRJ said in Microsoft Fail - SQL Server on Linux does not log successful logins:
Successful logins can be helpful because you can attach justification to them when they are occur if they are infrequent. For example connecting to a database at 2am on Saturday with no tickets or issues open a DB would be suspicious as hell.
Again I agree, but that is a breach. So you have to either trust the people with credentials or not. It can't be both ways. Based on the link you posted it looks like MS is pushing some other solution to try and get these kinds of details.
"but I suspect they are pushing Audit/XE for most of the way forward for those types of actions."
-
@DustinB3403 said in Microsoft Fail - SQL Server on Linux does not log successful logins:
I wouldn't say you're wrong to want this detail recorded, but if your credentials are compromised in such a way that anyone can log in to your database then you've already lost control.
That is not how things work.
Systems that connect to a DB (MS SQL or otherwise) almost always have the connection string in clear text.
Your system is hardened to prevent bad actor access to the system itself, but once the system is compromised, those credentials are also.
Also, insider threat where a dev puts in something from another system, etc.
The point of valid login logging will let get insight to know that your system is only being accessed where it should be.
-
@DustinB3403 said in Microsoft Fail - SQL Server on Linux does not log successful logins:
Again I agree, but that is a breach. So you have to either trust the people with credentials or not. It can't be both ways.
That statement is completely wrong. Do you not realize what least privilege, RBAC, and auditing is about?
-
@JaredBusch Look at what I stated. If someone is logging into your system and database, you've lost all control at that point. I recall this from previously asking why the heck Snipe-IT's database password was in plain text.
You taught me that - thank you.
@IRJ said in Microsoft Fail - SQL Server on Linux does not log successful logins:
That statement is completely wrong. Do you not realize what least privilege, RBAC, and auditing is about?
Auditing and control are different aspects, you can audit who is logging into your host. Adding additional tools to audit the individual database logins is what it looks like MS is pushing for (probably because there is a better way).
-
A few days ago, I needed to access a locked out SQL Server so I had to do steps like these to gain access to it.
https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/connect-to-sql-server-when-system-administrators-are-locked-out?view=sql-server-ver15- Open Command Prompt as Administrator - net stop "MSSQL$RMSQLDATA" && net start "MSSQL$RMSQLDATA" /m - Open SSMS as Administrator - Right click your login and select Properties - Go to Server Roles and select sysadmin and click OK - Close SSMS - net stop "MSSQL$RMSQLDATA" && net start "MSSQL$RMSQLDATA" -
Can you do something like this to log successful logins?
https://dba.stackexchange.com/a/19174
http://sqlandme.com/2011/07/13/sql-server-login-auditing-using-logon-triggers/
-
@black3dynamite said in Microsoft Fail - SQL Server on Linux does not log successful logins:
Can you do something like this to log successful logins?
https://dba.stackexchange.com/a/19174
http://sqlandme.com/2011/07/13/sql-server-login-auditing-using-logon-triggers/
I think something like that could work, but I am guessing we would have to mail out from the SQL server to report on it. It would not go into the SIEM which is an issue. I just wish it could write to a log file. It would be so much easier for us to just use the log file.
I am already importing the contents of the log file into our SIEM and have very useful alerting and rules on them.
-
It's not just about threats. Successful logins is also about audit trails, traceability, accountability, etc. Many places policy dictates all logins are recorded as well.
You always want to know who is logging into a system, even more so than who is failing.
-
@Obsolesce said in Microsoft Fail - SQL Server on Linux does not log successful logins:
It's not just about threats. Successful logins is also about audit trails, traceability, accountability, etc. Many places policy dictates all logins are recorded as well.
You always want to know who is logging into a system, even more so than who is failing.
Exactly. You expect accounts with SA level access to only log in from certain workstations. If, however suddenly, you see a lot of logins for my account from another computer/ip, something is up.
-
This post is deleted! -
@IRJ said in Microsoft Fail - SQL Server on Linux does not log successful logins:
@DustinB3403 said in Microsoft Fail - SQL Server on Linux does not log successful logins:
@IRJ said in Microsoft Fail - SQL Server on Linux does not log successful logins:
This is more about insider threat IMO
I agree, which still goes to the fact that if your credentials are comp'd, it doesn't matter what other security is in place.
Just like having a root password of "root", doesn't do much good to know that someone from <insert location> logged in. The damage is done.
As a point of "we know this occurred" sure I would love to have those details, but in the grand scheme that's like trying to create a CYA after a breach.
Successful logins can be helpful because you can attach justification to them when they are occur if they are infrequent. For example connecting to a database at 2am on Saturday with no tickets or issues open a DB would be suspicious as hell.
And, in some cases, you can do a "every log in is verified by a human". If you are using a modern app, generally there would be extremely few connections.
-
@dafyre said in Microsoft Fail - SQL Server on Linux does not log successful logins:
@Obsolesce said in Microsoft Fail - SQL Server on Linux does not log successful logins:
It's not just about threats. Successful logins is also about audit trails, traceability, accountability, etc. Many places policy dictates all logins are recorded as well.
You always want to know who is logging into a system, even more so than who is failing.
Exactly. You expect accounts with SA level access to only log in from certain workstations. If, however suddenly, you see a lot of logins for my account from another computer/ip, something is up.
Why would production systems have DB logins from workstations in general?
-
@scottalanmiller said in Microsoft Fail - SQL Server on Linux does not log successful logins:
@dafyre said in Microsoft Fail - SQL Server on Linux does not log successful logins:
@Obsolesce said in Microsoft Fail - SQL Server on Linux does not log successful logins:
It's not just about threats. Successful logins is also about audit trails, traceability, accountability, etc. Many places policy dictates all logins are recorded as well.
You always want to know who is logging into a system, even more so than who is failing.
Exactly. You expect accounts with SA level access to only log in from certain workstations. If, however suddenly, you see a lot of logins for my account from another computer/ip, something is up.
Why would production systems have DB logins from workstations in general?
You have to use a workstation to do any meaningful management with sql on Linux.
