This article is half-done without your Comment! *** Please share your thoughts via Comment ***
What do you know about the SQL Server Audit?
SQL Server Audit is used to track and log events (not data change) occurring on the SQL Server. SQL Server uses extended events to help create an audit. The audit can be server level or database level.
If you create an audit on a database table, SQL Server will only track the events that trigger the certain operations (SELECT/INSERT/UPDATE/DELETE) defined in the database audit specification.
What options are available to audit login activity?
- Custom solution with your application to log all logins into a centralized table
- Enable login auditing at the instance level in Management Studio
- Execute Profiler to capture logins into the instance
- Leverage a third party product
What is Audit in SQL Server?
Auditing an instance of the SQL Server Database Engine or an individual database involves tracking and logging events that occur on the Database Engine.
What is Server level Audit specification?
You can create one server audit specification per audit.
The server audit specification collects many server-level action groups raised by the Extended Events feature. You can include audit action groups in a server audit specification. Audit action groups are predefined groups of actions, which are atomic events occurring in the Database Engine.
What is Database level Audit specification?
You can create one database audit specification per SQL Server database per audit.
The database audit specification collects database-level audit actions raised by the Extended Events feature. You can add either audit action groups or audit events to a database audit specification.
Is it good to enable Audit only for SQL Server Login fails?
Yes:
Database administrators should enable login auditing feature of SQL Server on all instances which they manage. Once this feature is enabled SQL Server will write all the failed and successful login information in SQL Server Error Logs. It is always a best practice to auditing failed SQL Server logins.
Does SQL Server Audit require any additional permission?
Yes:
Each feature and command for SQL Server Audit has individual permission requirements.
Should we encrypt the SQL Server datatabase backups?
Yes:
We need to make sure the database backups are encrypted to avoid the misuse.
Why we should not use tha SA login for all the different SQL Server access?
Because, the sa login and password to all of your SQL Servers because auditing the sa login with a shared password is difficult without capturing the host name or some other personally identifiable data. We should migrate all SQL Server authentication to Windows based authentication.
Can I stop a System Admin from enabling SQL Server xp_cmdshell?
No:
T-SQL script to disable the SA login
|
1 |
ALTER LOGIN sa DISABLE; |
T-SQL script to rename the SQ login.
|
1 |
ALTER LOGIN sa WITH NAME = [dbrnd_user]; |
Please do auto reset the SA password and disable it.
SQL Server has an undocumented system stored procedure named sp_SetAutoSAPasswordAndDisable. This procedure will do exactly as the name suggests: it will reset the password and then disable the ‘sa’ login.
|
1 2 |
EXEC sp_SetAutoSAPasswordAndDisable GO |
|
1 2 3 |
SELECT name,type_desc,create_date,modify_date,password_hash FROM sys.sql_logins WHERE PWDCOMPARE('mssqltips',password_hash)=1; |
|
1 2 3 |
SELECT name,type_desc,create_date,modify_date,password_hash FROM sys.sql_logins WHERE PWDCOMPARE('',password_hash)=1; |
|
1 2 3 |
SELECT name,type_desc,create_date,modify_date,password_hash FROM sys.sql_logins WHERE PWDCOMPARE(name,password_hash)=1; |
GRANT:gives a user permission to perform certain tasks on database objects.
DENY:denies any access to a user to perform certain tasks on database objects.
REVOKE:removes a grant or deny permission from a user on certain database objects.
Sample T-SQL scripts for GRANT, DENY and REVOKE.
|
1 2 3 |
GRANT INSERT, UPDATE, SELECT ON Customers TO Joe, Mary; DENY DELETE ON Customers to Joe, Mary; REVOKE UPDATE ON Customers to Joe; |
