Jiansen Lu
SQL Server Security Interview Questions
August 22nd 2008 04:15
SQL Server Security Interview Questions
Problem
SQL Server Security, probably one of the most controversial and debated topics among SQL Server DBAs and Developers. One person's security is another person's nightmare and vice versa. With security being so important for so many different reasons let's try to determine some baseline interview questions although some of the responses can vary greatly based on the environment and industry. Good luck!
Solution
Question Difficulty = Easy
Question 1 - True or False - If you lose rights to your SQL Server instance the only option is to hack the registry.
False - If the Dedicated Administrator Connection (DAC) is setup this may be another way to access the SQL Server instance. Another option may be to use the BUILTIN\Administrators group. A final option may be to change registry values.
Additional information - Correct the SQL Server Authentication Mode in the Windows Registry
Question 2 - What objects does the fn_my_permissions function report on?
APPLICATION ROLE
ASSEMBLY
ASYMMETRIC KEY
CERTIFICATE
CONTRACT
DATABASE
ENDPOINT
FULLTEXT CATALOG
LOGIN
MESSAGE TYPE
OBJECT
REMOTE SERVICE BINDING
ROLE
ROUTE
SCHEMA
SERVER
SERVICE
SYMMETRIC KEY
TYPE
USER
XML SCHEMA COLLECTION
Question 3 - Name three of the features managed by the Surface Area Configuration tool.
Ad-hoc remote queries
Common language runtime
Dedicated Administrator Connection
Database Mail
Native XML Web Services
OLE Automation
Service Broker
SQL Mail
Web Assistant
xp_cmdshell
Additional information
Accessing the Windows File System from SQL Server
Enabling xp_cmdshell in SQL Server 2005
Dedicated Administrator Connection in SQL Server 2005
Question 4 - 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
Additional information - Who is logging in as the sa login in SQL Server?
Question Difficulty = Moderate
Question 1 - What is SQL Injection and why is it a problem?
SQL Injection is an exploit where unhandled\unexpected SQL commands are passed to SQL Server in a malicious manner. It is a problem because unknowingly data can be stolen, deleted, updated, inserted or corrupted.
Question 2 - What is the Guest user account? What login is it mapped to? Does it make sense to drop the Guest user account?
The Guest user account is created by default in all databases and is used when explicit permissions are not granted to access an object. It is not mapped directly to any login, but can be used by any login. Depending on your security needs, it may make sense to drop the Guest user account, in all databases except Master and TempDB, although sufficient testing should be conducted to validate applications will not break with this security restriction.
Question 3 - True or False - SQL Server 2005 certificates are only backed up via native database backups.
False - Certificates can also be backed up via the BACKUP CERTIFICATE command.
Question 4 - Name 3 of the features that the SQL Server 2005 built-in function LOGINPROPERTY performs on standard logins.
Date when the password was set
Locked out standard login
Expired password
Must change password at next login
Count of consecutive failed login attempts
Time of the last failed login attempt
Amount of time since the password policy has been applied to the login
Date when the login was locked out
Password hash
Additional information - Identify SQL Server 2005 Standard Login Settings
Question Difficulty = Difficult
Question 1 - How can SQL Server instances be hidden?
To hide a SQL Server instance, we need to make a change in SQL Server Configuration Manager. To do this launch SQL Server Configuration Manager and do the following: select the instance of SQL Server, right click and select Properties. After selecting properties you will just set Hide Instance to "Yes" and click OK or Apply. After the change is made, you need to restart the instance of SQL Server to not expose the name of the instance.
Question 2 - True or False - Profiler is the only tool that has the ability to audit and identify DDL events.
False - In SQL Server 2005 DDL triggers were introduced to audit CREATE, ALTER and DROP events for relational (stored procedures, functions, views, etc.) and security (certificates, logins, server, etc.) objects.
Question 3 - What are some of the pros and cons of not dropping the SQL Server BUILTIN\Administrators Group?
Pros:
Any Windows login is by default a SQL Server system administrator
This single group can be used to manage SQL Server from a system administrators perspective
Cons:
Any Windows login is by default a SQL Server system administrator, which may not be a desired situation
SQL Server BUILTIN\Administrators Group has system administrator rights by default
SQL Server itself does not need to be hacked to gain access to your data, if the Windows local administrators group is compromised then it is possible to access SQL Server as a system administrator
Question 4 - How can SQL Injection be stopped?
Development\DBA
Validate the SQL commands that are being passed by the front end
Validate the length and data type per parameter
Convert dynamic SQL to stored procedures with parameters
Remove old web pages and directories that are no longer in use because these can be crawled and exploited
Prevent any commands from executing with the combination of or all of the following commands: semi-colon, EXEC, CAST, SET, two dashes, apostrophe, etc.
Based on your front end programming language determine what special characters should be removed before any commands are passed to SQL Server
Depending on the language this could be semi-colon, dashes, apostrophes, etc.
Consider building a function to perform this action for both character and numeric data
Network Administration
Prevent traffic from particular IP addresses or domains
See if email based alerts can be sent if traffic comes from these sources
Review the firewall settings to determine if SQL Injection attacks can prevented
If you have a maintenance agreement with your firewall vendor see if you can update your product to prevent or alert on SQL Injection
Research products or services to scan your code and web site on a regular basis to prevent the issue
Really Long Link
Really Long Link
Really Long Link
Really Long Link
Really Long Link
Problem
SQL Server Security, probably one of the most controversial and debated topics among SQL Server DBAs and Developers. One person's security is another person's nightmare and vice versa. With security being so important for so many different reasons let's try to determine some baseline interview questions although some of the responses can vary greatly based on the environment and industry. Good luck!
Solution
Question Difficulty = Easy
Question 1 - True or False - If you lose rights to your SQL Server instance the only option is to hack the registry.
False - If the Dedicated Administrator Connection (DAC) is setup this may be another way to access the SQL Server instance. Another option may be to use the BUILTIN\Administrators group. A final option may be to change registry values.
Additional information - Correct the SQL Server Authentication Mode in the Windows Registry
Question 2 - What objects does the fn_my_permissions function report on?
APPLICATION ROLE
ASSEMBLY
ASYMMETRIC KEY
CERTIFICATE
CONTRACT
DATABASE
ENDPOINT
FULLTEXT CATALOG
LOGIN
MESSAGE TYPE
OBJECT
REMOTE SERVICE BINDING
ROLE
ROUTE
SCHEMA
SERVER
SERVICE
SYMMETRIC KEY
TYPE
USER
XML SCHEMA COLLECTION
Question 3 - Name three of the features managed by the Surface Area Configuration tool.
Ad-hoc remote queries
Common language runtime
Dedicated Administrator Connection
Database Mail
Native XML Web Services
OLE Automation
Service Broker
SQL Mail
Web Assistant
xp_cmdshell
Additional information
Accessing the Windows File System from SQL Server
Enabling xp_cmdshell in SQL Server 2005
Dedicated Administrator Connection in SQL Server 2005
Question 4 - 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
Additional information - Who is logging in as the sa login in SQL Server?
Question Difficulty = Moderate
Question 1 - What is SQL Injection and why is it a problem?
SQL Injection is an exploit where unhandled\unexpected SQL commands are passed to SQL Server in a malicious manner. It is a problem because unknowingly data can be stolen, deleted, updated, inserted or corrupted.
Question 2 - What is the Guest user account? What login is it mapped to? Does it make sense to drop the Guest user account?
The Guest user account is created by default in all databases and is used when explicit permissions are not granted to access an object. It is not mapped directly to any login, but can be used by any login. Depending on your security needs, it may make sense to drop the Guest user account, in all databases except Master and TempDB, although sufficient testing should be conducted to validate applications will not break with this security restriction.
Question 3 - True or False - SQL Server 2005 certificates are only backed up via native database backups.
False - Certificates can also be backed up via the BACKUP CERTIFICATE command.
Question 4 - Name 3 of the features that the SQL Server 2005 built-in function LOGINPROPERTY performs on standard logins.
Date when the password was set
Locked out standard login
Expired password
Must change password at next login
Count of consecutive failed login attempts
Time of the last failed login attempt
Amount of time since the password policy has been applied to the login
Date when the login was locked out
Password hash
Additional information - Identify SQL Server 2005 Standard Login Settings
Question Difficulty = Difficult
Question 1 - How can SQL Server instances be hidden?
To hide a SQL Server instance, we need to make a change in SQL Server Configuration Manager. To do this launch SQL Server Configuration Manager and do the following: select the instance of SQL Server, right click and select Properties. After selecting properties you will just set Hide Instance to "Yes" and click OK or Apply. After the change is made, you need to restart the instance of SQL Server to not expose the name of the instance.
Question 2 - True or False - Profiler is the only tool that has the ability to audit and identify DDL events.
False - In SQL Server 2005 DDL triggers were introduced to audit CREATE, ALTER and DROP events for relational (stored procedures, functions, views, etc.) and security (certificates, logins, server, etc.) objects.
Question 3 - What are some of the pros and cons of not dropping the SQL Server BUILTIN\Administrators Group?
Pros:
Any Windows login is by default a SQL Server system administrator
This single group can be used to manage SQL Server from a system administrators perspective
Cons:
Any Windows login is by default a SQL Server system administrator, which may not be a desired situation
SQL Server BUILTIN\Administrators Group has system administrator rights by default
SQL Server itself does not need to be hacked to gain access to your data, if the Windows local administrators group is compromised then it is possible to access SQL Server as a system administrator
Question 4 - How can SQL Injection be stopped?
Development\DBA
Validate the SQL commands that are being passed by the front end
Validate the length and data type per parameter
Convert dynamic SQL to stored procedures with parameters
Remove old web pages and directories that are no longer in use because these can be crawled and exploited
Prevent any commands from executing with the combination of or all of the following commands: semi-colon, EXEC, CAST, SET, two dashes, apostrophe, etc.
Based on your front end programming language determine what special characters should be removed before any commands are passed to SQL Server
Depending on the language this could be semi-colon, dashes, apostrophes, etc.
Consider building a function to perform this action for both character and numeric data
Network Administration
Prevent traffic from particular IP addresses or domains
See if email based alerts can be sent if traffic comes from these sources
Review the firewall settings to determine if SQL Injection attacks can prevented
If you have a maintenance agreement with your firewall vendor see if you can update your product to prevent or alert on SQL Injection
Research products or services to scan your code and web site on a regular basis to prevent the issue
Really Long Link
Really Long Link
Really Long Link
Really Long Link
Really Long Link
| 58 |
| Vote |
Add To: 
subscribe to this blog
Comment by TimmyH
Tech News
Can you HACK it?
Genyration