Microsoft Fail - SQL Server on Linux does not log successful logins
-
@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.
-
@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.
As another point, this isn't at all impossible, public DHCP addresses change all of the time. So if a user was working from home or traveling they could get a different IP every 8 hours.
-
@DustinB3403 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:
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.
As another point, this isn't at all impossible, public DHCP addresses change all of the time. So if a user was working from home or traveling they could get a different IP every 8 hours.
In that one example, it would be from internal to external. That you might be able to track usefully.
-
Of course, in most cases you'd have an application which would connect to the database and never actually "login" in the ways we've discussed so far unless you needed to manually edit the database.
-
@DustinB3403 said in Microsoft Fail - SQL Server on Linux does not log successful logins:
I think Scott was asking why do you need a physical workstation connected to your SQL database.
Um.... what else am I going to use? I gotta have something to use to run my SSH Client or SSMS.
-
@DustinB3403 said in Microsoft Fail - SQL Server on Linux does not log successful logins:
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).
I don't argue that you could do this. However, tools like SSMS are great for syntax checking and providing other utility that, while could be done from a CLI session are more difficult.
This is especially true when altering stored procedures or running complex queries.
-
@dafyre 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:
I think Scott was asking why do you need a physical workstation connected to your SQL database.
Um.... what else am I going to use? I gotta have something to use to run my SSH Client or SSMS.
But it doesn't get attached directly to the database. It's external either over the LAN or WAN.
-
@DustinB3403 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:
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.
As another point, this isn't at all impossible, public DHCP addresses change all of the time. So if a user was working from home or traveling they could get a different IP every 8 hours.
My point was about connections from unexpected ip addresses / places.
Mobile users should be connected via VPN (or SSH Tunnel or ZT or Jump Box) or some other method that is known and expected.
-
@DustinB3403 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:
@DustinB3403 said in Microsoft Fail - SQL Server on Linux does not log successful logins:
I think Scott was asking why do you need a physical workstation connected to your SQL database.
Um.... what else am I going to use? I gotta have something to use to run my SSH Client or SSMS.
But it doesn't get attached directly to the database. It's external either over the LAN or WAN.
You are confusing me here. Are you talking about being directly attached to the physical server?
-
First of all you use a bastion host, so you have to SSH which is obvious. You still need to connect to the database. The bastion host only allows incoming connections from VPN.
SO you need,
-
VPN connection with MFA
-
Bastion Host Connection
-
Database Connection
You cant just use some random IP.
-
