SECURITIES

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

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 

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

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 

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.  

How to resolve the orphan use problem?

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

USE <database_name>;

 GO;

 sp_change_users_login @Action='Report';

 GO; 

To resolve the orphan user problem

USE <database_name>;

 GO

 sp_change_users_login @Action='update_one',

@UserNamePattern='<database_user>',

 @LoginName='<login_name>';

 GO 

What are the fixed server level roles?

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 

What are the Database roles?

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. 

What are the security related catalog views?

Where the security related information stored on?

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 

 What are the extra roles available in msdb?

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 

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

Option1: Use the Dedicated Administrator Connection

Option2: Use BUILTIN\Administrators Group

Option3: Change Registry Values 

What objects does the fn_my_permissions function reports on?

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’)

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 

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 

How to perform backup for Certificates in sql server?

Using Native Backup

Using Backup Certificate Command 

Name 3 of the features that the SQL Server 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 

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.

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

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. 

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 

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.

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

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

Prevent traffic from particular IP addresses or domains

Review the firewall settings to determine if SQL Injection attacks can prevented

Remove old web pages and directories that are no longer in use because these can be crawled and exploited

Research products or services to scan your code and web site on a regular basis to prevent the issue

http://www.acunetix.com/vulnerability-scanner/

http://www.rapid7.com/nexpose/web-application-va.jsp

http://www.fortify.com/products/detect/

http://www.mcafeesecure.com/us/

http://www.onlinehackscan.com/default.asp 

How to recover from SQL Injection?

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 

How to enforce Security in SQL SERVER?

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 

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?

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. 

What will you do if you lost rights of your SQL Server instance?

We can use the below options

1.Dedicated Administrator Connection

2.BUILIN\Administrator Group (Incase its rights are not revoked)

3.Final Option is to change the registry value

4.You can change authentication mode via registry  

What is SQL Injection?

SQL Injection is developed 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.  

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

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  

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

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

Members of which database role(s) can give other users access to the database? (Select all correct answers.)

1. sysadmin
2. db_accessadmin
3. db_securityadmin
4. securityadmin 

Members of which roles can back up a database? (Select all correct answers.)

1. dbcreator
2. db_owner
3. serveradmin
4. db_backupoperator

If you grant permissions on a table to the Public role and later revoke permission from the Windows NT Accounting group, does someone who is a member of that group still have permissions on the table? Select the best answer.

a.Yes, because the REVOKE would only remove a permission that you granted to the Accounting group, so it has no effect.

b.No, because the REVOKE to the group removes the inherited permission to Public.

c.No, because the REVOKE from the Accounting group removes all permissions for members of that group, however they were assigned.

d.You can't tell from the information supplied. 

You want to give all members of the NT Accounting group except the summer interns access to the payroll table. The best way to do this is:

1. Grant database access to the Accounting group, but deny access to the interns.
2. Grant database access to the Accounting group, assign them the correct permissions, and revoke permissions from the interns.
3. Grant database access to the Accounting group, assign them the correct permissions, and deny permissions to the interns.
4. Add another NT group that contains the accountants but not the interns, and give this group the correct access and permissions.

Types of startup Service accounts? 

Local User Account: This user account is created in your server where SQL Server is installed, this account does not have access to network resources.
Local Service Account: This is a builtin windows account that is available for configuring services in windows. This account has permissions as same as accounts that are in the users group, thus it has limited access to the resources in the server. This account is not supported for SQL SERVER and AGENT services.
Local System Account: This is a builtin windows account that is available for configuring services in windows. This is a highly privileged account that has access to all resources in the server with administrator rights.
Network Service Account: This is a builtin windows account that is available for configuring services in windows. This has permissions to access resources in the network under the computer account.
Domain Account: This account is a part of your domain that has access to network resources for which it is intended to have permission for. It is always advised to run SQL Server and related services under a domain account with minimum privilege need to run SQL Server and its related services. 

Where can you see the Local System, Local Service and Network Service accounts? These are windows in-built accounts that are part of the operating system and assigned to the users, you cannot use the account to login to the system, these accounts are meant to be used for securing and authentication mechanism. 

How to check If SQL Server is suing Kerberos authentication?

SELECT net_transport, auth_scheme FROM sys.dm_exec_connections WHERE session_id = @@spid

What are the steps you will take, if you are tasked with securing an SQL Server?

Again this is another open ended question. Here are some things you could talk about:

·         Preferring NT authentication,

·         using server,

·         databse and application roles to control access to the data,

·         securing the physical database files using NTFS permissions,

·         using an unguessable SA password,

·         restricting physical access to the SQL Server,

·         renaming the Administrator account on the SQL Server computer,

·         disabling the Guest account,

·         enabling auditing,

·         using multiprotocol encryption,

·         setting up SSL,

·         setting up firewalls, 

 isolating SQL Server from the web server etc. 

What is permission to read only operation on jobs, views and sp? 

Managed to get the ability to view all logins.

GRANT VIEW ANY DEFINITION TO <Read-Only-User-Who-Sees-Everything>

Managed to get the ability to view all jobs on SQL Server Agent:

EXEC sp_addrolemember 'SQLAgentReaderRole', '<ReadOnly-User-Who-Sees-Everything>'  

please explain me how to give defualt permissions to a user on tempdb?

-In the temp db, all database/server logins have all permissions by default.You need not to grant any permission.-

-To my knowledge you dont need to specify explicit permission. Any login who is granted to access instance will have permission to access tempdb too

-Tempdb is the shared area for all sessions, so if u are using table and temporary tables sometimes excessive query memory grants it will spill to tempdb, hence no permission required at all.

- since tempdb is created everytime by inheriting the properties of model, if you provide a permission in model database for a user then that same permission will be inherited by tempdb also.