By: Jeremy Kadlec | Comments (8) | Related: More > Professional Development Interview Questions DBA
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
- Additional information - Script to determine permissions in SQL Server 2005
- 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
- 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.
- Additional information - Recover from a SQL Injection Attack on SQL Server
- 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.
- Additional information - SQL Server Database Guest User Account
- 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.
- Additional information - SQL Server 2005 Encryption - Certificates 101
- 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.
- Additional information - Hiding instances of SQL Server 2005
- 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.
- Additional information - Auditing DDL (Create, Alter, Drop) Commands in SQL Server 2005
- Question 3 - What are some of the pros and cons of not dropping the SQL Server BUILTIN\Administrators Group?
- Pros:
- Any Windows login in that group is by default a SQL Server system administrator
- This single group can be used to manage administrators from a Windows and SQL Server 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
- Additional information - Security Issues with the SQL Server BUILTIN\Administrators Group
- Pros:
- 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
- Prevent traffic from particular IP addresses or domains
- Additional information - Recover from a SQL Injection Attack on SQL Server
- Development\DBA
Next Steps
- If you are interested in more SQL Server security related information on MSSQLTips.com, check out the following resources:
- Security category with 100+ tips
- Recover from a SQL Injection Attack on SQL Server
- Identify SQL Server 2005 Standard Login Settings
- Correct the SQL Server Authentication Mode in the Windows Registry
- Script to determine permissions in SQL Server 2005
- Natively Encrypting Social Security Numbers in SQL Server 2005
- Assigning DBA Rights in SQL Server
- Error - Windows cannot access the specified device, path or file.
- For more SQL Server DBA and\or Developer interview questions, check out these tips:
- Do you have some common SQL Server interview questions that were not included in this tip that you ask as a portion of your interviews? Feel free to post them in the forum below.
About the author
Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
