Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server DBA Security Interview Questions


By:   |   Last Updated: 2008-08-20   |   Comments (8)   |   Related Tips: 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.
  • 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.
  • 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.
  • 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
  • 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
    • Additional information - Recover from a SQL Injection Attack on SQL Server
Next Steps


Last Updated: 2008-08-20


get scripts

next tip button



About the author
MSSQLTips author Jeremy Kadlec Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an MSSQLTips.com co-founder and Edgewood Solutions SQL Server Consultant.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Thursday, January 24, 2013 - 3:28:25 PM - Gene Wirchenko Back To Top
I found the instructions on how to reveal the answers to be very simple. I very much appreciated the answers being hidden. I could read the question without having to worry about reading half of the answer. Some of us can read at glance or even find it difficult not to. Putting the answers right by the questions is a bother for me. Putting the answers elsewhere means a lot of scrolling. Hiding the answers is great. Thinking on this more: Why not put the answers after all of the questions and have page anchors on each question to each answer. It is still not good as what you had, but it would address my concern.

Thursday, January 24, 2013 - 12:11:56 PM - Greg Robidoux Back To Top

@Gene - thanks for the feedback. 

The answers used to be hidden, but we had so many comments from people that we should include the answers even though they were there you just needed to highlight them.  So to make this easier we just made the answers visible.

Greg Robidoux


Thursday, January 24, 2013 - 11:36:36 AM - Gene Wirchenko Back To Top
Too bad you show the answers. It is too easy to read part of the answer when reading the question. (Not all of us read one word at a time.) I much prefer the highlighting.

Monday, December 31, 2012 - 9:29:36 AM - Jeremy Kadlec Back To Top

KaptKos,

Sorry for any confusion, I have removed the need to highlight the answers in order to see them.  HTH.

Thank you,
Jeremy Kadlec


Friday, December 28, 2012 - 8:31:56 AM - KaptKos Back To Top

Would be nice to see your answers.  Are they from experience or by textbook?

There's a big difference if you answer from one or the other.


Friday, April 13, 2012 - 9:37:23 AM - tcstl Back To Top

Need questions about roles, database and server level


Thursday, August 21, 2008 - 5:16:02 PM - admin Back To Top

DavidB,

Agreed and thank you for the feedback.  We stated a similar message in the problem statement. 

As with many aspects of SQL Server, security certainly can be many shades of gray.

Thank you,
The MSSQLTips.com Team


Wednesday, August 20, 2008 - 11:12:44 AM - DavidB Back To Top
Nice set of questions. Some questions have a textbook answer while others force the interviewee to use there knowledge and experience to create a reasonable answer.

Learn more about SQL Server tools