MSSQL Security – Interview Questions and Answers

sql server security interview questions

 

Database Process Error.jpg

1. What is the Guest user account in SQL Server?  What login is it mapped to it?  

Ans:

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

2. What is the use of BUILTIN\Administrators Group in SQL Server?

Ans:

Any Windows login in BUILTIN\Administrators 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

3. We have a list of 3 SQL Server logins which are dedicated to a critical application. We have given all required rights to those logins. Now my question is we have to restrict the access only to these three logins. Means there are two conditions:

a) No other user should be able to access the database except those three logins

b) Even for those three logins they should be able to run their queries only through the application. If someone login through SSMS and trying to run a query should result into a failure.

Finally there should be only way to running a query is from their application using one of those three logins, there should be no other way to run queries on that database. How do you restrict?

Ans:

  • Do not give access to any other login on that database except for those 3 app logins.
  • Create a trigger that test each and every query like below

IF app_name() in(‘SQL Query Analyzer’,’Microsoft SQL Server Management Studio’)
raiserror (…..)
Return

4. How to resolve the orphan use problem?

Ans:

Such a user is said to be an orphaned user of the database on that server instance. A database user can become orphaned if the corresponding SQL Server login is dropped. Also, a database user can become orphaned after a database is restored or attached to a different instance of SQL Server. Orphaning can happen if the database user is mapped to a SID that is not present in the new server instance.

  • To find out the orphan users

 

  • To resolve the orphan user problem

 

5. What are the fixed server level roles?

Ans:

  • SysAdmin – Can perform any activity
  • ServerAdmin – Can change server configuration, restart, shutdown server
  • SecurityAdmin – Can manage server level logins, also can manage db level if they have permission on db
  • Granted: ALTER ANY LOGIN
  • ProcessAdmin – Can kill a process on an instance
  • Granted: ALTER ANY CONNECTION, ALTER SERVER STATE
  • DiskAdmin – Can manage the disk files
  • Granted: ALTER RESOURCES
  • BulkAdmin – Can perform BULK INSERT
  • Granted: ADMINISTER BULK OPERATIONS
  • SetupAdmin – Can add and remove linked servers
  • Granted: ALTER ANY LINKED SERVER
  • Dbcreator – Can create, alter, drop and restore any database on the instance
  • Granted: CREATE ANY DATABASE
  • Public – Default role for newly created login

sp_helpsrvrolemember : List out the members mapped with the server roles

6. What are the Database roles?

Ans:

  • db_accessadmin – Granted: ALTER ANY USER, CREATE SCHEMA, Granted with Grant option – Connect
  • db_backupoperator – Granted: BACKUP DATABASE, BACKUP LOG, CHECKPOINT
  • db_datareader – Granted – SELECT
  • db_datawriter – Granted – INSERT, UPDATE and DELETE
  • db_ddladmin – Granted – Any DDL operation
  • db_denydatareader – Denied – SELECT
  • db_denydatawriter – Denied – INSERT, UPDATE and DELETE
  • db_owner – Granted with GRANT option: CONTROL
  • db_securityadmin – Granted: ALTER ANY APPLICATION ROLE, ALTER ANY ROLE, CREATE SCHEMA, VIEW DEFINITION
  • dbm_monitor – Granted: VIEW most recent status in Database Mirroring Monitor

sp_helprolemember : List out the members mapped with the server roles

Note:

Fixed database roles are not equivalent to their database-level permission. For example, the db_owner fixed database role has the CONTROL DATABASE permission. But granting the CONTROL DATABASE permission does not make a user a member of the db_owner fixed database role.

7. What are the security related catalog views?

Where the security related information stored on?

Ans:

  • Server Level:
  • Sys.server_permissions
  • Sys.server_principals
  • Sys.server_role_members
  • Sys.sql_logins
  • Database Level:
  • Sys.database_permissions
  • Sys.database_principals
  • Sys.database_role_members

8. What are the extra roles available in msdb?

Ans:

  • db_ssisadmin: Equals to sysadmin
  • db_ssisoperator: Import/Delete/Change Role of own packages
  • db_ssisltduser: Only can view and execute the packages
  • dc_admin : Can administrate and use the data collector
  • dc_operator: Can administrate and use the data collector
  • dc_proxy : Can administrate and use the data collector
  • PolicyAdministratorRole: can perform all configuration and maintenance activities on Policy-Based Management policies and conditions.
  • ServerGroupAdministratorRole : Can administrate the registered server group
  • ServerGroupReaderRole: Can view and the registered server group
  • dbm_monitor: Created in the msdb database when the first database is registered in Database Mirroring Monitor

9. If you lose rights to your SQL Server instance what are the options to connect to SQL SERVER Instance?

Ans:

Option1: Use the Dedicated Administrator Connection

Option2: Use BUILTIN\Administrators Group

Option3: Change Registry Values

10. What objects does the fn_my_permissions function reports on?

Ans:

  • SERVER
  • DATABASE
  • SCHEMA
  • OBJECT
  • USER
  • LOGIN
  • ROLE
  • APPLICATION ROLE
  • TYPE
  • MESSAGE TYPE
  • ASYMMETRIC KEY
  • SYMMETRIC KEY
  • CERTIFICATE
  • SERVICE
  • REMOTE SERVICE BINDING
  • FULLTEXT CATALOG
  • ASSEMBLY
  • CONTRACT
  • ENDPOINT
  • ROUTE
  • XML SCHEMA COLLECTION

SELECT * FROM fn_my_permissions(NULL, ‘SERVER’);

SELECT * FROM fn_my_permissions(‘AdventureWorks’, ‘DATABASE’);

SELECT * FROM fn_my_permissions(‘Employee’, ‘OBJECT’)

11. Name three of the features managed by the Surface Area Configuration tool.

Ans:

  • 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

12. What options are available to audit login activity?

Ans:

  • 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

 

13. How to perform backup for Certificates in sql server?

Ans:

  • Using Native Backup
  • Using Backup Certificate Command

14. Name 3 of the features that the SQL Server built-in function LOGINPROPERTY performs on standard logins.

Ans:

  • 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

15. How can SQL Server instances be hidden?

Ans:

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.

16. Is Profiler the only tool that has the ability to audit and identify DDL events?

Ans:

No. 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.

17. What are some of the pros and cons of not dropping the SQL Server BUILTIN\Administrators Group?

Ans:

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

18. What is SQL Injection and why is it a problem?

Ans:

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.

19. How can SQL Injection be stopped?

Ans:

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
  • 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

Network Administration

20. How to recover from SQL Injection?

Ans:

If for some reason the resolution implemented does not resolve the problem and the SQL Injection attack occurs again, the quickest path may be to do the following:

  • Shut down the web sites
  • Review the IIS logs to determine the commands issued and which web page\command has the vulnerability
  • Convert the code to determine which tables were affected and the command issued
  • Find and replace the string in your tables
  • Correct the web page\command that has the vulnerability
  • Test to validate the issue no longer occurs
  • Deploy the web page\command
  • Re-enable the web sites

21. How to enforce Security in SQL SERVER?

Ans:

By providing strong Passwords, Limited the access to make sure right people have access to the right data, Creating Customized database roles, server roles and assign privileges and by choosing the correct authentication mode etc.

A DBA should be careful in providing security…..General precautions includes:

  • Minimize the number of sysadmins allowed to access SQL Server.
  • Give users the least amount of permissions they need to perform their job.
  • Use stored procedures or views to allow users to access data instead of letting them directly access tables.
  • When possible, use Windows Authentication logins instead of SQL Server logins.
  • Don’t grant permissions to the public database role.
  • Remove user login IDs who no longer need access to SQL Server.
  • Avoid creating network shares on any SQL Server.
  • Turn on login auditing so you can see who has succeeded, and failed, to login.
  • Ensure that your SQL Servers are behind a firewall and are not exposed directly to the Internet.
  • Using server, database and application roles to control access to the data
  • Securing the physical database files using NTFS permissions
  • Using an un guessable SA password
  • Restricting physical access to the SQL Server
  • Disabling the Guest account
  • Isolating SQL Server from the web server
  • Choose either of the service to run SQL Server (Local User – Not an Admin , Domain User – Not an Admin)
  • Restrict the remote administration (TC)
  • If SQL Server authentication is used, the credentials are secured over the network by using IPSec or SSL, or by installing a database server certificate.
  • Do not use DBO users as application logins
  • Firewall restrictions ensure that only the SQL Server listening port is available on the database server.
  • Remove the SQL guest user account.
  • Remove the BUILTIN\Administrators server login.
  • Apply the latest security updates / patches

We have plenty of features in SQL SERVER to enforce the security. The major features include:

  • Password policies
  • Encryption
  • Limited metadata visibility (system Tables to Catalog Views)
  • DDL triggers
  • User-schema separation
  • Impersonation
  • Granular permission sets
  • Security catalog views

In addition to these features we have some more added in SQL SERVER 2008, like Policy Based Management, Security Audit, Improved Encryption, Backup Security etc.

When we talk about the security we have to consider the bellow

  • Patches and Updates
  • Services
  • Protocols
  • Accounts
  • Files and Directories
  • Shares
  • Ports
  • Registry
  • Auditing and Logging
  • SQL Server Security
  • SQL Server Logins, Users, and Roles
  • SQL Server Database Objects

22. You are delegating permissions on your SQL Server 2005 server to other administrators. You have local, single server jobs on one server that you would like to allow another administer to start, stop, and view the history for, but not delete history. This administrator will own the jobs. Which role should you assign?

Ans:

SQLAgentUserRole
SQL Server 2005 provides 3 fixed roles for the agent service that limit privileges for administrators. The SQLAgentUserRole is designed for local jobs (not multiserver) that allow the member to work with their owned jobs (edit, start, stop, view history) without deleting the history of any job.

Posted in Interview Q&A, SQL Development, SQL Server DBA | Tagged , , , , , , , , | 3 Comments

Leave a Reply

3 Comments on "MSSQL Security – Interview Questions and Answers"

Notify of
avatar
Sort by:   newest | oldest | most voted
Ravi
Guest

Good stuff man.

koti
Guest

nice articles thank you…………….

wpDiscuz