Interview Questions

Friday, August 21, 2015

SQL SERVER Database in RESTORING State for Long Time

A very interesting question I received the other day.
“Our database has been in restoring stage for a long time. We have already restored all the necessary files there. After restoring the files we are expecting that  the database will be in operational mode, however, it is continuously in the restoring mode. Any suggestion?”
The question is very common. I sent user follow up emails to understand what is actually going on with the user. I realized after restoring their bak files and log files their database was in the restoring state because they had not restored the latest log file with RECOVERY options.
recoveryoption SQL SERVER   Database in RESTORING State for Long Time
As they had completed all the database restore sequence (bak and log in order), the real need for them was to recover the database from norecovery state. User can restore log files till the database is no recovery mode. If the database is recovered it will be in operation and it can continue database operation. If the database has another operations we cannot restore further log as the chain of the log file after the database is recovered is meaningless. This is the reason why the database has to be norecovery state when it is restored.
There are three different ways to recover the database.
1) Recover the database manually with following command.
RESTORE DATABASE database_name WITH RECOVERY
2) Recover the database with the last log file.
RESTORE LOG database_name FROM backup_device WITH RECOVERY
3) Recover the database when bak is restored
RESTORE DATABASE database_name FROM backup_device WITH RECOVERY

How to Move TempDB to New Drive in SQL Server

Introduction
This article explains the steps you must follow to move TempDB database from one drive to another in SQL Server. However, for the changes to come into effect you must restart SQL Server Service.
Overview of Steps to move TempDB data and log files to new location are:-
1. Identify the location of TempDB Data and Log Files
2. Change the location of TempDB Data and Log files using ALTER DATABASE
3. Stop and Restart SQL Server Service
4. Verify the File Change
5. Delete old tempdb.mdf and templog.ldf files
Identify the location of TempDB Data and Log Files
In the New Query window of SQL Server Management Studio, execute the below mentioned script to identify the location of TempDB data and log file.
Use master
GO

SELECT
name AS [LogicalName]
,physical_name AS [Location]
,state_desc AS [Status]
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb');
GO

Location of TempDB Data and Log File in SQL Server
Once you have identified the location of TempDB files then the next step will be to create the respective folders on the new drive where you would like to store the TempDB data and log file. However, you need to make sure that the new location where the TempDB files are stored is accessible by SQL Server. i.e., you need to ensure that the Account under which SQL Server Service is running has read and write permissions on the folder where the files are stored.
Change the location of TempDB Data and Log files using ALTER DATABASE
Execute the below ALTER DATABASE command to change the location of TempDB Data and Log file in SQL Server.
USE master;
GO

ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'T:\MSSQL\DATA\tempdb.mdf');
GO

ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'T:\MSSQL\DATA\templog.ldf');
GO
Once the above script has executed successfully you will receive a message to restart SQL Server Service for the changes to come into effect.
The file "tempdev" has been modified in the system catalog. The new path will be used the next time the database is started.
The file "templog" has been modified in the system catalog. The new path will be used the next time the database is started.
Stop and Restart SQL Server Service
Stop and restart the instance of SQL Server for the changes to come into effect.
Verify the File Change
Execute the below TSQL to verify whether TempDB Data and Log files are residing in the new location.
Use master
GO

SELECT
name AS [LogicalName]
,physical_name AS [Location]
,state_desc AS [Status]
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb');
GO
Delete old tempdb.mdf and templog.ldf files
Final step will be to delete the tempdb.mdf & templog.ldf files from the original location.
Important Note: SQL Server doesn’t support moving TempDB Database using backup/restore and by using detach database methods.
Error Message Received when you try Backup and Restore Method
Msg 3147, Level 16, State 3, Line 1
Backup and restore operations are not allowed on database tempdb.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
Error Message Received when you try Detach Method
Msg 7940, Level 16, State 1, Line 1
System databases master, model, msdb, and tempdb cannot be detached.
Conclusion
This article explains the steps you must follow to move TempDB database from one drive to another in SQL Server.

SQL SERVER Backup Timeline and Understanding of Database Restore Process in Full Recovery Model

I assume you all know that there are three types of Database Backup Models, so we will not discuss on this commonly known topic today. In fact, we will just talk about how to restore database that is in full recovery model.
In general, databases backup in full recovery mode are taken in three different kinds of database files.
  1. Full Database Backup
  2. Differential Database Backup
  3. Log Backup
What really perplexes most people is differential database backup.
Let me narrate here a real story. One of the DBAs in local city once called me up and laughingly said that he has just found something not smart about SQL Server Product Team in terms of database backup. I just could not believe this negative remark against SQL Server Product Team so I asked him to explain me what is it. He told me that in his opinion it is an extra step when it is about Differential Database backup. I asked him how he restores his database. He replied that he starts with first full database backup and then sequentially all log backups and differential database backups. He continued his explanation and said that he has figured something interesting; that is, if he does not restore all the differential database backups and only restores log backups it just works fine. According to him this is an unnecessary step.
Well, I am extremely happy to say he is wrong. He has totally failed to understand the concept of differential database backup. I called up another friend in his company and told him this story and he found it funny too! He suggested that he will explain to my friend that he needs to do all differential backups first and then all log backups after the last differential backup. I was again amazed and didn’t know what to do. He was wrong too!
After interacting with many DBAs I have realized that it is quite confusing to most of the people how differential database is useful and many are not aware of the correct method to restorefull recovery model. Before I start explaining please understand the following diagram where I have demonstrated time line when a backup was taken.
Let us remember the golden rule for restore first.

‘After restoring full database backup, restore latest differential database backup and all the transaction log backup after that to get database to current state.’
From the above-listed rule, it is very clear that there is no need to restore all the differential database backups when restoring databases. You can only restore the latest Differential database backup. Differential database backup is the backup of all the changes made in database from last full backup; it is cumulative itself. That is the reason why the size of next differential database backup is much more than the previous differential database backup. All differential database backups contain all the data of previous differential database backups. You just have to restore the latest differential database backup and right after that install all the transaction database backups to bring database to the current state.
If you do not want to have differential database backup and have all the transaction log backups, in that case, you will have to install all the transactional database backups, which will be very time consuming and is not recommended when disastrous situation is there and getting server back online is the priority. In this way, differential database backups are very useful to save time as well as are very convenient to restore database. Instead of restoring many transaction database logs, which needs to be done very carefully without missing a single transaction in between, this is very convenient.
In our example, there are multiple paths to get server to the current state.
Path 1 (SLOWEST) : Full Database Restore >> Log Backup 0_1 >> Log Backup 1_1 to all remaining logs.
Path 2 : Full Database Restore >> Differential Database Backup 1 >> Log Backup 1_1 to all remaining logs.
Path 3 (FASTEST) : Full Database Restore >> Differential Database Backup 2 >> Log Backup 2_1 to all remaining logs.

Auditing Failed Logins in SQL Server

ProblemAn auditor has taken a look at our SQL Servers and has told us that we need to audit login failures to the SQL Servers themselves. How do we do this?
SolutionSQL Server permits the auditing of both login successes and failures, depending on your need. The way to turn this auditing on is by using SQL Server Management Studio. Connect to the SQL Server in Object Explorer and then right-click on the SQL Server and choose the Properties option from the pop-up menu. You should see the server properties like so:
Server Properties
You'll want to click on the Security page (highlighted in the previous image) which will bring you to where you can set the login auditing:
Security Page
There are four options available:
  • None - Neither successful nor failed logins will be audited.
  • Failed logins only - Failed logins will be audited, but successful logins will be ignored.
  • Successful logins only - Successful logins will be audited, but failed logins will be ignored.
  • Both failed and successful logins - Login will be audited regardless of success and failure.
When you've made your choice, click the OK button. The setting for login auditing is actually a registry entry which differs based on SQL Server version and SQL Server instance name. SQL Server only reads this setting when the SQL Server service starts up. Therefore, for this setting to take effect, you'll need to restart the SQL Server service. Because it is a registry setting, the only easy way to set how to audit is through the GUI. There isn't a way using T-SQL without using one of the extended stored procedures that hits the registry directly.
A word about the various settings. At the very least, you should be auditing failed logins on production systems. There may be cases where it is necessary to audit successful logins, but realize that auditing successful logins will likely generate a lot of events to sift through. On extremely sensitive systems it is important to have this level of control. However, on less critical systems, you may be better served to only audit failed logins so you don't get overwhelmed with the number of events. If you have a log management solution, by all means audit both.

Once you've turned on auditing, the next step is to be able to find the results of said auditing. The first place where the audit information is recorded is the SQL Server log. For instance, an audit success by the "sa" account and an audit failure are shown in the following excerpt:
SQL Server log - auditing
If you want to view the contents of the SQL Server log by using T-SQL, there's an undocumented (but well known) extended stored procedure called xp_readerrorlog. You can use it to dump the results of the error log to a recordset by:
EXEC xp_readerrorlog;
To read further back, you can give this extended stored procedure an integer parameter which corresponds to the order of the SQL Server log. 0 represents the current log, with each number after that referring to the next one back. So to see the contents of the 3rd log (including the current one) you would pass a parameter of 2 (counting up from 0 - 0, 1, 2 would be the third), you would execute:
EXEC xp_readerrorlog 2;
To search the current error log and only return failed logins you can use the following command.  The first parameter specifies the error log (0=current), the second parameter specifies the type of log (1=SQL Error Log) and the third parameter specifies the message to search for.
 EXEC sp_readerrorlog 01'Login failed' 

If you have log management software, another good place to look is the Application event log for the operating system. You can view this by using the Computer Management tool. If you expand System Tools and then Event Viewer, you'll see the Application event log like so:
Computer Management - App Event Log
If you look in this event log, you'll be looking for events with a source of MSSQLSERVER or MSSQL$<Instance Name>. For instance, the following shows the audit success and failure events we looked at before in the SQL Server log:
App Event Log - SQL Server Login Events
Successful logins for SQL Server 2005 and 2008 will have an event ID of 18454 and failed logins will have an event ID of 18456. SQL Server 2000 uses the same event ID for both, making it impossible to determine of the event signifies a success or failure without looking at the event details. As a result, I would recommend only auditing failures to eliminate the confusion. Once you have determined the correct events, you can look at the event log details to see what login was attempted. For instance, here is the failed login attempt:
Event - SQL Server Failed Login 
and here is the successful login attempt:
Event - SQL Server Login Success
The details of what account was attempted is stored within the description of the event log. Therefore, if you need to automate the retrieval of audit successes or failures, make sure the application can retrieve the details and parse the text accordingly. 

SQL Server Security Checklist

Problem
I have to install a new Microsoft SQL Server box and configure it as a production server. I know that there are many security related settings and I do not want anyone to hack the system on the first day on the network. Which are the basic steps to harden the security of my SQL Server? Do you have a checklist to start with? Check out this tip to learn more.
Solution
There are many security related settings in the Microsoft SQL Server and you should also consider setting up processes to ensure that the security is maintained in the future. The security related tasks can be divided into four main categories: physical security, operating system level security, SQL Server configuration and user management. You should protect your server physically, have a secure OS and then you can start thinking about your SQL Server.
Physical Security
The first line of your security is the physical security of your on premise hardware. You have to protect your server from being tampered with. Here are the basic items I would recommend:
  • Limit the number of employees who have access to the physical hardware. You can limit access with access codes, entry cards or even with armed security guards. The most important point is to have a minimal number of people who have access and you should have written procedures to access the server, preferably with managerial approval for physical access rights.
  • Store the backup media at a secure offsite location. To increase security, it is recommended to have one location for the production server and a separate location for the backup files.
  • Configure alerts for hardware warnings. To be proactive, you can configure e-mail alerts for example with HP iLO or Dell DRAC, but you can also use VMware vSphere.
Operating System Security
We live in the era of the internet, so physical security is not enough. You should build your protection plan to include Windows. The operating system should be secured to reduce the vulnerability of your system. Here are the basic items to consider:
  • Install all service packs and critical fixes for Windows (and for VMware if applicable). It is necessary to implement all critical fixes to ensure that there are no known holes on your security. Consider creating a process to apply the latest Windows security patches on a regular basis.
  • Configure a firewall. You need a protective firewall on your server to defend your system. If there is no other firewall installed on your server, then configure Windows Firewall to work with the Database Engine, Integration Services and Analysis Services components of SQL Server.
  • Limit the number of employees who have Windows Administrator access on the SQL Server. As a best practice, you should limit the administrative access only to those who really require it. This should include the VMware console as well as Remote Desktop Services. Maybe you can consider limiting the OS level admin access to the SQL Server administrators and Network administrators.
SQL Server Installation
Now we reached the point when you can finally work with your beloved SQL Server. There are a number of security related tasks which should be completed before you can start to use a production SQL Server. Here is what I recommend as a starting point:
  • Install only the required components. The less installed components, the less security problems can occur.
  • Install all service packs and critical fixes for SQL Server. It is necessary to implement all critical fixes to ensure that there are no known holes on your security.
  • Disable unnecessary features and services. You can use the SQL Server Configuration Manager to disable unused SQL Server services.
  • Disable the unused SQL Server protocols. SQL Server supports four type of protocols: Shared Memory, Named Pipes, TCP/IP and VIA. You should use the bare minimum of these protocols and disable the others using the SQL Server Configuration Manager.
  • Change the default SQL Server ports. The SQL Server gets installed with the default TCP port 1433 which is well known by admins and attackers too. You can avoid some targeted SQL attacks if you do not use the default ports. You can change the port in SQL Server Configuration Manager.
  • Hide the SQL Server instance and/or turn off the SQL Server Browser Service. You can configure the SQL Server instance as hidden using the SQL Server Configuration Manager. This will prevent advertisement of your server by the SQL Server Browser service. If you use fully qualified connection strings then you can disable the SQL Server Browser.
  • Restrict the access to the SQL Server configuration and database file. Apart from the database level access, you should also protect the file system to prevent unauthorized file deletion, copying or alteration of data.
  • Restrict the access to the SQL Server backup folders. Read Below tip to learn how to protect your SQL Server Backup folder.
  • Use Transparent Data Encryption whenever it is an option. You can secure your data, logs and backup with TDE in Evaluation, Developer, Enterprise and Datacenter versions of SQL Server 2008, 2008 R2 and 2012.
  • Create only the required databases. Do not create demo or test databases on production servers, keep it clean and safe.
  • Run the SQL Server Best Practice Analyzer to verify your installation. The Microsoft SQL Server 2012 Best Practice Analyzer can quickly identify if your server is configured according to industry best practices or not.
  • Revoke execute rights to 'PUBLIC' on extended stored procedures. Extended stored procedures will be removed in a future version of SQL Server and it is not recommended to use them. You can use CLR Integration instead. The following extended stored procedures should not be executed by your applications:xp_availablemedia, xp_dirtree, xp_enumgroups, xp_fixeddrives, xp_regaddmultistring, xp_regdeletekey, xp_regdeletevalue, xp_regenumvalues, xp_regremovemultistring, xp_regwrite, xp_regread, xp_servicecontrol, xp_subdirs.
  • Disable the xp_cmdshell option. It is highly recommended to disable the xp_cmdshell stored procedure even if other administrators can enable it again.
User Accounts
After your basic SQL Server security is configured, you can start to address the traditional user access and security topics. The administrators and the service accounts require extra attention. Here is how to get started:
  • Rename and disable the SA account if your applications allow it. You can use thesp_SetAutoSAPasswordAndDisable stored procedure to disable the SA account as described in below tip. This will prevent the attacker from trying to login with the default admin account.
  • Remove the BUILTIN\Administrators group from the SQL Server Logins. You can read more about the security issues with the SQL Server BUILTIN\Administrators group in Below tip.
  • Use Windows Authentication mode. You can check and change the authentication mode in three different ways: using SQL Server Management Studio, with T-SQL or in the Windows registry.
  • Every administrator should have a named login, shared logins should not be allowed. This is required in order to be able to identify the people behind each and every database change. It is also critical to have an up-to-date list of all the accounts.
  • All accounts for named user access should be controlled by Active Directory. Use Active Directory and do not create SQL Server logins. It makes the administration easier if you grant access rights through Active Directory groups or Group Policy.
  • Use service accounts for applications. It is a best practice to create a different service account with a descriptive name for every service. You can use SQL Server logins, but a complex password is a must. Restrict the access only to data required: if an application updates only 1-2 tables then it does not require full control of every object in the database.
  • Configure service accounts with the least privileges. You can read Below tip if you would like to know how to determine service related privileges for SQL Server service account. Do not grant more rights than required.
  • The user privileges should be minimized. Try to assign the minimum sufficient rights to every user. It is a best practice to document any elevated user permission and request managerial approval.
  • All administrator accounts should have a complex password and password change should be enforced. You should identify blank and weak passwords and configure password enforcement options.
  • Configure SQL Server login auditing to log both failed and successful logins. Details of the login audit configuration can be found in Below tip and there is also a tip about SYSADMIN login auditing below.
The above checklist can be used to ensure that the minimum requirements are fulfilled. Every company should have an information security policy and you should apply those requirements to your SQL Server as well. In case that policy does not exist then you can use this checklist as the basic checklist.


Tips:  


1.Protecting the SQL Server Backup folder



If you look at the permission for the BACKUPS folder that the Microsoft SQL Server install creates, you've probably noted that it has permissions where members of the Administrators group has full control, meaning they can do anything they want. Actually, this is true of most every location, including a folder I just created off the root of the C:\ drive.
Advanced Security Settings for a Windows Directory
In a lot of cases the Windows Local Administrators group has inherited the permissions from a higher level folder. The solution is to break inheritance and set our own permissions. Without breaking inheritance we can only add permissions to a file or folder. We can't remove permissions, so this is a necessary step. However, to make sure you don't lock yourself out, choose to copy the existing inheritable permissions.
Windows Security - Click Add to convert and add inherited parent permissions as explicit permissions on this object
Before you remove any permissions, the first step I'd take is to ensure you have permission to get back and manage the folder. For instance, if all DBAs should be able to get to the appropriate folder, add the appropriate Windows group for the DBAs and give it Full Control. This allows you to modify the folder and any files contained in it as well as manage the permissions. It's this last aspect of Full Control you'll find essential. I typically grant this permission, then click OK to confirm and only then do I start removing or reducing permissions.
When I go to remove permissions, I tend to automatically eliminate the following accounts or groups:
  • SYSTEM
  • Authenticated Users
  • Users
Note I didn't strike the Administrators group. Instead, I tend to reduce permissions.  I still grant the local Windows Administrators the ability to see what's in the folder. Unless the local security policy has been changed, they can backup the files and folders regardless of permissions, but this doesn't typically give them the ability to view what's in the folder without the use of a 3rd party product. When it comes to disk space issues and the like, it can often be helpful for an administrator to be able to see the files. We can accomplish this by granting the following permission:List folder contents. This gives them the ability to see the files, but not read from them.
Backups Folder Properties
Most importantly, don't forget to give Microsoft SQL Server access. It will need Modify access for the folder. This gives SQL Server the ability to read files, write to them, modify them, and delete them. In SQL Server 2005-2008R2, there will be a SQL Server User group corresponding to the instance name. In SQL Server 2012 the access will be configured based on the service's virtual account name. Check the appropriate version of Books Online for this. Here's the pattern for SQL Server 2008 and 2008R2:
Type of InstancePattern for Group Name
Default InstanceSQLServerMSSQLUser$ComputerName$MSSQLSERVER
Named InstanceSQLServerMSSQLUser$ComputerName$InstanceName
If you don't have the appropriate access, you'll get the following error trying to run a backup through SQL Server Management Studio:
Access is denied error message from SQL Server Management Studio when you do not have permissions to the backup directory
If you get this error, you're likely missing the Modify permission for SQL Server.
Assign the Modify permission for SQL Server service account on the Backup Folder
And that's all there is to it. While a member of the Windows Local Administrators group could still get in, he or she would have to intentionally seek to bypass security. You can't completely prevent an administrator from gaining access, however, by making it difficult you keep out the merely curious. That's the intent of altering file and folder permissions like we did here.



2.Secure and disable the SQL Server SA Account



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.

The procedure takes no parameters, so the syntax for usage is as follows:
EXEC sp_SetAutoSAPasswordAndDisable
GO
After completion you should see the standard message:
Command(s) completed successfully. 
The actual code is as follows:
ALTER procedure [sys].[sp_SetAutoSAPasswordAndDisable]
as
    -- can execute only as SysAdmin
 if (not (is_srvrolemember('sysadmin') = 1))  -- Make sure that it is the SA executing this.
 begin
  raiserror(15247,-1,-1)
  return(1)
 end
    -- Begin a transaction
    BEGIN TRANSACTION
    
 -- Disable Password Policy on the SA Login
 ALTER LOGIN sa WITH CHECK_POLICY = OFF
 IF @@ERROR <> 0
 BEGIN
  ROLLBACK TRANSACTION
  RETURN (1)
 END
 -- Create a New Guid as the random password
 declare @randompwd UNIQUEIDENTIFIER
 declare @stmt nvarchar(4000)
 SET @randompwd = newid()
 SELECT @stmt = 'ALTER LOGIN sa WITH PASSWORD = ' + quotename(@randompwd, '''')
 EXEC(@stmt)
 IF @@ERROR <> 0
 BEGIN
  ROLLBACK TRANSACTION
  RETURN (1)
 END
 -- Now set the policy back
 ALTER LOGIN sa WITH CHECK_POLICY = ON
 IF @@ERROR <> 0
 BEGIN
  ROLLBACK TRANSACTION
  RETURN (1)
 END 
 -- Now set the policy back
 ALTER LOGIN sa DISABLE
 IF @@ERROR <> 0
 BEGIN
  ROLLBACK TRANSACTION
  RETURN (1)
 END 
 -- Commit the transaction
 COMMIT TRANSACTION
When you execute this stored procedure the password for the ‘sa' login will be reset to a random GUID, and then be disabled. Auditors love this aspect because not only is the password secure, but so is the account itself.
If you need to roll your own solution to rotate the password for the ‘sa' login, then the sp_SetAutoSAPasswordAndDisable stored procedure may be exactly what you are looking for.



3.How to determine service related privileges for Sql Server service account



I had a customer inquire as to how to verify service-related permissions for the Sql Server service account...i.e., Books Online indicates the Sql Server service account requires permission to start the following related services (amoung many other requirements): Sql Server Active Directory Helper and Sql Writer services.  Obviously, the service really wouldn't NEED permissions to do so if you weren't going to make use of said services, but let's assume for the moment that we are going to do so, and that you want to determine first of all if the Sql Server service account has these permissions and/or what permissions the account has to services on the machine in question.
There's a tool called "AccessChk" which will allow you to see this type of information, along with lots of other access-related information for given accounts. You can find information on the tool at the following link:
As an example, the following command line will give you effective permissions to all services on the local machine for an account named "LTCBOYDMS\sqlService":
 accesschk "LTCBOYDMS\sqlService" -vc *
To determine service-related permissions, the -c option must be used.  Without it, you get by default ACL information for files/folders/shares and other information (which is handy also). If you specify the wildcard character (the *), you'll get permission related information for the given account against ALL services on the machine...if you're looking for only a specific service information, you can replace the wildcard with the appropriate service name.  For example, to get effective permissions to only the SQLWriter service (the service name for the Sql Writer service), you could use the following command:
 accesschk "LTCBOYDMS\sqlService" -vc "SQLWriter"
and, for the Active Directory Helper service, something like the following:
 accesschk "LTCBOYDMS\sqlService" -vc "MSSQLServerADHelper"
Output from the command should look similar to the following:
 C:\Projects\Toolbox\accessCheck>accesschk.exe "DUMMYDOMAIN\svcSqlService" -vc *
 AccessChk v2.0 - Check account access of files, registry keys or services

 RW Alerter
  SERVICE_ALL_ACCESS
 RW ALG
  SERVICE_ALL_ACCESS
 RW MSSQL$SQL2000
  SERVICE_ALL_ACCESS
 RW MSSQL$SQL2005
  SERVICE_ALL_ACCESS
 RW MSSQL$SQL2005B
  SERVICE_ALL_ACCESS
 RW MSSQL$SQLEXPRESS
  SERVICE_ALL_ACCESS
 RW MSSQLServerADHelper
  SERVICE_ALL_ACCESS
 ...
 R  Pml Driver HPZ12
  SERVICE_QUERY_STATUS
  SERVICE_QUERY_CONFIG
  SERVICE_INTERROGATE
  SERVICE_ENUMERATE_DEPENDENTS
  SERVICE_PAUSE_CONTINUE
  SERVICE_START
  SERVICE_STOP
  SERVICE_USER_DEFINED_CONTROL
  READ_CONTROL
 ...
 RW SQLBrowser
  SERVICE_ALL_ACCESS
 RW SQLWriter
  SERVICE_ALL_ACCESS
 ...
 C:\Projects\Toolbox\accessCheck>
In the output, the "RW" designators at the beginning of each line indicate Read/Write privs (if present), then the service name, then using the -v option will provide you the additional output shown below each service above.  The SERVICE_ALL_ACCESS implies all access, if you don't have that, you should see individual service related privs like in the line above for the "Pml Driver HPZ12" service.



4.Auditing Failed Logins in SQL Server



SQL Server permits the auditing of both login successes and failures, depending on your need. The way to turn this auditing on is by using SQL Server Management Studio. Connect to the SQL Server in Object Explorer and then right-click on the SQL Server and choose the Properties option from the pop-up menu. You should see the server properties like so:
Server Properties
You'll want to click on the Security page (highlighted in the previous image) which will bring you to where you can set the login auditing:
Security Page
There are four options available:
  • None - Neither successful nor failed logins will be audited.
  • Failed logins only - Failed logins will be audited, but successful logins will be ignored.
  • Successful logins only - Successful logins will be audited, but failed logins will be ignored.
  • Both failed and successful logins - Login will be audited regardless of success and failure.
When you've made your choice, click the OK button. The setting for login auditing is actually a registry entry which differs based on SQL Server version and SQL Server instance name. SQL Server only reads this setting when the SQL Server service starts up. Therefore, for this setting to take effect, you'll need to restart the SQL Server service. Because it is a registry setting, the only easy way to set how to audit is through the GUI. There isn't a way using T-SQL without using one of the extended stored procedures that hits the registry directly.
A word about the various settings. At the very least, you should be auditing failed logins on production systems. There may be cases where it is necessary to audit successful logins, but realize that auditing successful logins will likely generate a lot of events to sift through. On extremely sensitive systems it is important to have this level of control. However, on less critical systems, you may be better served to only audit failed logins so you don't get overwhelmed with the number of events. If you have a log management solution, by all means audit both.

Once you've turned on auditing, the next step is to be able to find the results of said auditing. The first place where the audit information is recorded is the SQL Server log. For instance, an audit success by the "sa" account and an audit failure are shown in the following excerpt:
SQL Server log - auditing
If you want to view the contents of the SQL Server log by using T-SQL, there's an undocumented (but well known) extended stored procedure called xp_readerrorlog. You can use it to dump the results of the error log to a recordset by:
EXEC xp_readerrorlog;
To read further back, you can give this extended stored procedure an integer parameter which corresponds to the order of the SQL Server log. 0 represents the current log, with each number after that referring to the next one back. So to see the contents of the 3rd log (including the current one) you would pass a parameter of 2 (counting up from 0 - 0, 1, 2 would be the third), you would execute:
EXEC xp_readerrorlog 2;
To search the current error log and only return failed logins you can use the following command.  The first parameter specifies the error log (0=current), the second parameter specifies the type of log (1=SQL Error Log) and the third parameter specifies the message to search for.
 EXEC sp_readerrorlog 01'Login failed' 

If you have log management software, another good place to look is the Application event log for the operating system. You can view this by using the Computer Management tool. If you expand System Tools and then Event Viewer, you'll see the Application event log like so:
Computer Management - App Event Log
If you look in this event log, you'll be looking for events with a source of MSSQLSERVER or MSSQL$<Instance Name>. For instance, the following shows the audit success and failure events we looked at before in the SQL Server log:
App Event Log - SQL Server Login Events
Successful logins for SQL Server 2005 and 2008 will have an event ID of 18454 and failed logins will have an event ID of 18456. SQL Server 2000 uses the same event ID for both, making it impossible to determine of the event signifies a success or failure without looking at the event details. As a result, I would recommend only auditing failures to eliminate the confusion. Once you have determined the correct events, you can look at the event log details to see what login was attempted. For instance, here is the failed login attempt:
Event - SQL Server Failed Login 
and here is the successful login attempt:
Event - SQL Server Login Success
The details of what account was attempted is stored within the description of the event log. Therefore, if you need to automate the retrieval of audit successes or failures, make sure the application can retrieve the details and parse the text accordingly. 



5. SQL Server SysAdmin Login Auditing



In SQL Server 2005 Service Pack 2, Microsoft introduced logon triggers into the core functionality. Like DDL and DML triggers, these triggers fire on particular events, in this case, whenever a logon to the SQL Server instance occurs. We can use a logon trigger to audit when members of a particular role, such as the syadmin fixed server role, logs on.
In order to audit for members of the sysadmin fixed server role, we will need to use two system views:sys.server_role_members and sys.server_principals. We will join these views when a logon event occurs to determine if the logon is a member of the sysadmin role or not. We can get the members by joining these two views together as shown below:
SELECT sp.principal_idFROM sys.server_role_members srmINNER JOIN sys.server_principals sp     ON srm.member_principal_id = sp.principal_idWHERE srm.role_principal_id = (     SELECT principal_id     FROM sys.server_principals     WHERE [Name] = 'sysadmin')
This query will be the basis for our logon trigger. By adding an AND clause in our final set of code, we will be able to test whether or not the incoming logon is a member of the sysadmin fixed server role.
We will also need to some place to record the event when it occurs. One of the easiest ways to do this is to use a table created for this purpose in a work database. For the purposes of this example, I am going to assume the table can be stored in a DBA database by the name of DBAWork.  Here is the associated code:
CREATE TABLE dbo.AuditSysAdminLogin(AuditEventId INT IDENTITY(1,1) NOT NULL,EventTime DATETIME NOT NULL,ServerLogin NVARCHAR(100) NOT NULL,CONSTRAINT PK_AuditSysAdminLogin PRIMARY KEY CLUSTERED (AuditEventID));GO
Once we have the audit table, we can create our logon trigger. The basic syntax for a logon trigger is similar to a DDL trigger as shown below:
CREATE TRIGGER <trigger name>
ON ALL SERVER
FOR LOGON
AS
<SQL Statements>
Following this format and using the query identified above to help identify who is a member of the SysAdmin role, the only aspect we are missing is a way to identify the logon.  There is a system function, ORIGINAL_LOGIN(), which provides that information. Putting it all together, here is our logon trigger:
USE masterGO
CREATE TRIGGER trigLogon_CheckForSysAdmin
            ON ALL SERVER
            FOR LOGON
            AS
    BEGIN
    IF 
EXISTS (
        
SELECT sp.principal_id
            FROM sys.server_role_members srm
                    JOIN sys.server_principals sp
                    ON srm.member_principal_id sp.principal_id
            WHERE role_principal_id (
            
SELECT principal_id 
                FROM sys.server_principals 
                WHERE NAME 'sysadmin')
                AND 
ORIGINAL_LOGIN() sp.NAME)
        
BEGIN
        INSERT INTO 
DBAWork.dbo.AuditSysAdminLogin
            (EventTimeServerLogin)
                
VALUES
            
(GETDATE(), ORIGINAL_LOGIN())
        
END;
    
END
GO
Next Steps
  • Be sure to have SQL Server 2005 SP2 or later installed in order to have this functionality work properly.  
  • This tip provides a simple approach to capture the logins of SQL Server SysAdmins, but can be easily modified for other groups, roles or logins.
  • The logic can also be modified to prevent logins from accessing the SQL Server instance during particular hours, to limit the number of sessions, etc.  So think about some of the needs you have in your environment and consider this tip as a stepping stone to address those needs.
  • As you experiment with this logic, please provide your problems and solutions in the forum below.  We would love to hear how creative you were with the opportunities available with this favor of SQL Server triggers.