SQL Server: Database Security Interview Questions and Answers (Day-4)

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

In our organization we have two teams that stored encrypted data in Microsoft SQL Server. The data is encrypted using symmetric keys and those keys are encrypted with passwords. However, we have a director who needs to be able to see the data for both teams, but we’d like it so that he only has to remember one password, preferably separate from the passwords used by the two teams. Is that possible?

Yes, it is. Microsoft SQL Server supports the ability for encryption keys to be encrypted via multiple mechanisms. For instance, a symmetric key can be encrypted with a password and an asymmetric key. If you have access to the symmetric key and at least one of those mechanisms, you can open the key.

What is hash function?

A hash function takes in data and returns back a fixed length block of bits such that any change to the data should result in a different block.

What do you know about the HASHBYTES()?

HASHBYTES(), as the name implies, is a function or algorithm that generates a hash from some input.

Which are the different hashing algorithms?

MD2, MD4, MD5, SHA-0, SHA-1, SHA-2

What is the main limitation of hasing function?

Since a hash function will return a fixed length block, obviously, there are a finite set of possibilities for the output. Therefore, there’s bound to be different inputs that will result in the same output. We call these situations collisions. What is of concern is if a collision can be engineered fairly quickly/cheaply. This is referred to as a collision attack.

What do you know about the Symmetric Encryption Algorithm?

This algorithm either take unencrypted data and return encrypted data, or they take encrypted data and return unencrypted data. In order to do so, they use a key, and the key is the same regardless of which direction you go. When it comes to encryption algorithms, symmetric encryption algorithms are substantially faster than asymmetric algorithms.

What are the different type of Symmetric Encryption Algorithms?

• DES (DES)
• Triple DES with 128 bit key (TRIPLE_DES)
• Triple DES with 192 bit key (DESX)
• RC2 (RC2)
• RC4 (RC4)
• RC4 with 128 bit key (RC4_128)
• AES with 128 bit key (AES_128)
• AES with 192 bit key (AES_192)
• AES with 256 bit key (AES_256)

What is your common practice with account SA?

• Set a hard to guess password.
• Rename sa.
• Disable sa.
• Ensure that no other accounts exist named sa.

A user asked how they could get an alert whenever a login failed due to the account being locked out?

• A SQL Server Agent alert, using the WMI event AUDIT_LOGIN_FAILED
• Event Notifications, also using AUDIT_LOGIN_FAILED
• Manually consuming the error log

How to hide your SQL Server code?

The WITH ENCRYPTION clause is used to encrypt the definition of SQL Server objects (views, stored procedures, functions ), so that no one may be able to view the definition/code of that specific object. Using the clause while creating an object is fairly easy.

What is the main disadvantage of WITH ENCRYPTION option to hide the SQL Server code?

The definition/code of object will not be available to anyone regardless of their permissions or access level. You can not customize the permissions for code/definition visibility.

Once an object has been created on the server using the WITH ENCRYPTION option, there is no standard method provided to get the definition/code back out of the encrypted object. You will need to keep a copy of the code outside of SQL Server.

What is the alternative of WITH ENCRYPTION oprtion to secure the SQL Server code?

The simple alternative is to granting or revoking View Definition rights.

If permission View Definition is denied for an object to any user then the user would not be able to view the object in SSMS or view.

What is a Salt?

It is basically something added to whatever we’re trying to encrypt to make it harder to decrypt the data (two way functions, like symmetric and asymmetric key functions) or find a collision (one way functions, AKA hash functions). The salt should be potentially different for every single piece of encrypted data. The salt should be randomly generated.

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

• Dedicated Administrator Connection
• BUILIN\Administrator Group (Incase its rights are not revoked)
• Final Option is to change the registry value
• You can change authentication mode via registry

What is the difference between sysadmin and serveradmin server role?

sysadmin: Members of the sysadmin fixed server role can perform any activity in the server.

serveradmin: Members of the serveradmin fixed server role can change server-wide configuration options and shut down the server.

How to check SA login related information?