Explore topic-wise InterviewSolutions in .

Always Encrypted:

• This is a new feature which is useful for managing highly sensitive data
• Unlike TDE it encrypts data at rest means physical files (Data, Log and Backup), data in memory and data in communication channels.
• TEMPDB is uninvolved from encryption
• Encryption can be applied to column level.
• A driver that encrypts and decrypts the data as it is sent to the database SERVER is installed on the client.
• Application connection string must be changed.

Row Level Security:

• This is first introduced in Azure SQL Database. Now it’s part of on-premises feature from SQL Server 2016.
• Data NEED not be encrypted but we can restrict the users to see the sensitive data. No master keys or certificates required as there is no encryption
• Row-level security is based on a table-valued function which evaluates user access to the table based on a security policy that is applied to the table.
• The access levels only applies to SELECT, UPDATE, DELETE operations but anyone who is having INSERT permissions can insert rows.
• Only problem with this is using user defined functions to control user access which is a huge disadvantage from performance prospect.

Dynamic Data Masking:

• Masks data at select TIME based on user or database roles (Mainly for Read-only Users).
• It actually doesn’t change the data but mask data based on the user who access that data.

For example:

I have a columns called “CredNo” to store customer creditcard NUMBER. If I mask this column then it will be viewed as 22XXXXXXXXXX56.

But as I said data is not modified only this logic applied and data is masked based on the user/role.

A SYSADMIN or db_owner can VIEW the actual data.

We can use 4 different types of functions to mask data: Email, Partial, Default, Random

Always Encrypted:

Row Level Security:

Dynamic Data Masking:

For example:

I have a columns called “CredNo” to store customer creditcard number. If I mask this column then it will be viewed as 22XXXXXXXXXX56.

But as I said data is not modified only this logic applied and data is masked based on the user/role.

A SYSADMIN or db_owner can view the actual data.

We can use 4 different types of functions to mask data: Email, Partial, Default, Random

Functionality Enhancement for TDE: In 2014 Transparent DATA Encryption takes the normal backup and then APPLIES the Encryption before writing it to the disk. It allows backup compression is useful when TDE enabled. TDE applies on COMPRESSED backup.

CONNECT ANY DATABASE: This is a new server level permission which can allow a login to connect all existing and future databases in the instance. This can be helpful when we need to give permissions for audit purpose.

IMPERSONATE ANY LOGIN: This is a new server level permission which gives us more control in giving/denying impersonate access to logins.

SELECT ALL USER SECURABLES: A new server level permission. When granted, a login such as an auditor can view data in all databases that the user can connect to.

Functionality Enhancement for TDE: In 2014 Transparent Data Encryption takes the normal backup and then applies the Encryption before writing it to the disk. It allows backup compression is useful when TDE enabled. TDE applies on compressed backup.

CONNECT ANY DATABASE: This is a new server level permission which can allow a login to connect all existing and future databases in the instance. This can be helpful when we need to give permissions for audit purpose.

IMPERSONATE ANY LOGIN: This is a new server level permission which gives us more control in giving/denying impersonate access to logins.

SELECT ALL USER SECURABLES: A new server level permission. When granted, a login such as an auditor can view data in all databases that the user can connect to.

Default Schema for Windows Group Logins: Let’s say we have a Windows account [MyDomain WinAdmin]. If someone from this group logged in [MyDomainUser1] and tried to create an object then there will be a new schema created like [MyDomainUser1].Table. This issue got FIXED in 2012. In 2012 we can assign a default schema for the Windows Group accounts.

User Defined Server ROLES: Till 2008 R2 we have user defined roles at database level, 2012 allows us to create a Server level user defined roles which gives us more control in handling security.

Contained Database: Easier database migrations as it contains user and login information on same database instead of in Master.

Data Protection: Supporting Hash Algorithm-256 (SHA-256) and SHA-512.

Auditing: Native support/feature for auditing the database environment by creating the Audit specifications. We can also create user defined audits.

Ex: We can create an Audit specification to TRACE all events for a SPECIFIC login and write all these event details into Audit Log. We can also filter the events.

Default Schema for Windows Group Logins: Let’s say we have a Windows account [MyDomain WinAdmin]. If someone from this group logged in [MyDomainUser1] and tried to create an object then there will be a new schema created like [MyDomainUser1].Table. This issue got fixed in 2012. In 2012 we can assign a default schema for the Windows Group accounts.

User Defined Server Roles: Till 2008 R2 we have user defined roles at database level, 2012 allows us to create a Server level user defined roles which gives us more control in handling security.

Contained Database: Easier database migrations as it contains user and login information on same database instead of in Master.

Data Protection: Supporting Hash Algorithm-256 (SHA-256) and SHA-512.

Auditing: Native support/feature for auditing the database environment by creating the Audit specifications. We can also create user defined audits.

Ex: We can create an Audit specification to trace all events for a specific login and write all these event details into Audit Log. We can also filter the events.

Application roles are DATABASE level roles like database roles. We can create them and assign permissions to them just like regular database roles but we can’t map users with them. Instead, we PROVIDE a password to unlock access to the database.

Here it is how it works:

• Create a login on SQL SERVER for application user
• Create an application role on the corresponding database.
• Give the application role password to the user
• User will have access to login to SQL Server but doesn’t have any access to the database INCLUDING public role.
• He/she just need to provide the password to unlock the access to the database
• EXEC sp_addapprole ‘App_Role_Name’, ‘Password’
• Once it is executed successfully the user will get all rights that your app role have on that database.

Application roles are database level roles like database roles. We can create them and assign permissions to them just like regular database roles but we can’t map users with them. Instead, we provide a password to unlock access to the database.

Here it is how it works:

SQLAgentUserRole:

SQL Server 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.

SQLAgentUserRole:

SQL Server 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.

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 BUILTINAdministrators 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 below:

• 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

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:

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

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 below:

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 pagecommand 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 pagecommand that has the vulnerability
• Test to validate the issue no longer occurs
• Deploy the web pagecommand
• Re-enable the web sites

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:

DevelopmentDBA:

• 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

DevelopmentDBA:

SQL Injection is an exploit where unhandledunexpected 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.

SQL Injection is an exploit where unhandledunexpected 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.

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

Pros:

Cons:

Any Windows login is by default a SQL Server system administrator, which may not be a desired situation

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.

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.

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.

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.

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

Date when the password was set

• USING NATIVE BACKUP
• Using Backup CERTIFICATE COMMAND

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

Custom solution with your application to log all logins into a centralized table

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

Ad-hoc remote queries

• SERVER
• DATABASE
• CHEMA
• 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’)

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

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

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

OPTION1: Use the DEDICATED Administrator CONNECTION

OPTION2: Use BUILTINAdministrators GROUP

Option3: Change Registry Values

Option1: Use the Dedicated Administrator Connection

Option2: Use BUILTINAdministrators Group

Option3: Change Registry Values

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

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

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

Server Level:

Database Level:

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

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.

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

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

To find out the ORPHAN USERS:

USE <database_name>;

GO;

sp_change_users_login @Action='Report';

GO;

To resolve the orphan USER PROBLEM:

10USE <database_name>;

GO

sp_change_users_login @Action='update_one', 

@UserNamePattern='<database_user>',

@LoginName='<login_name>';

GO

To find out the orphan users:

USE <database_name>;

GO;

sp_change_users_login @Action='Report';

GO;

To resolve the orphan user problem:

10USE <database_name>;

GO

sp_change_users_login @Action='update_one', 

@UserNamePattern='<database_user>',

@LoginName='<login_name>';

GO

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

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

Any Windows login in BUILTINAdministrators 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

Any Windows login in BUILTINAdministrators 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

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

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