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.
-
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.
-
@IRJ said in Microsoft Fail - SQL Server on Linux does not log successful logins:
@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.
What kind of meaningful? We don't have one hooked up to ours.
-
@scottalanmiller 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:
@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.
What kind of meaningful? We don't have one hooked up to ours.
You likely don't have a justification for anybody having SA privileges on your server, then.
For somebody who works primarily as a DBA, they would need SA level access to do some things. Thus, they would use their workstation to log into the SQL server with whatever management tools they want to use.
If I always connect from 192.168.60.60 and suddenly, I'm connecting from 200.100.50.10, then that should be cause for some alarm.
-
I think Scott was asking why do you need a physical workstation connected to your SQL database.
You'd SSH into your SQL server as a server user, and if you had to from there login to the SQL database as the admin (or another SQL user).
-
@dafyre said in Microsoft Fail - SQL Server on Linux does not log successful logins:
If I always connect from 192.168.60.60 and suddenly, I'm connecting from 200.100.50.10, then that should be cause for some alarm.
This goes to the point of, you'd track your SSH logins, not the SQL database logins.
Edit for clarity: You'd track your SSH logins first, and then if you needed you'd monitor the database.
