Sql-server – when to open and close symmetric keys

Q1a: Is the master key password created per DB instance?

A1a: Assuming the question really means "Is the master key created per DB?"" then answer is Yes. Each DB has an different master key. there is also a thing called the service master key, which is per SQL Server instance.

Q1b: When I backup that DB (.bak) will I be able to restore the DB to another Server2?

A1b: Yes. The master key in the restored database can be open using the original password. Then the Server2 service master key encryption can be added to the restored DB master key.

Q2: Do I need to backup the certificate, master key or symmetric key if I want to restore to another server?

A2: No. All these objects are part of the database and they are restored along with the database. Specific needs to backup individual keys may arise from operational requirements (eg. key escrow).

Q3: When should the symmetric key be opened?

A3: Keys that require opening have to be opened in the session. Once opened, they stay open until explicitly closed or until the session disconnects. An 'open' key is 'open' only in the session that opened it.

Q4: Should I worry about who can open and close the symmetric key? ...

A4: Now this is the real question. You have two alternatives really, which correspond to two distinct scenarios:

Scenario A: when the service needs to access encrypted data without asking the user for passwords to the data. This is the vast majority of the cases. In this case the service needs to open the keys somehow. the solution is that the SQL Server uses the service master key to encrypt the database master key and the database master key is used to encrypt the certificate's private key and the private key is used to encrypt a symmetric key and the symmetric key encrypts the data. The SQL Server itself can reach any data following this chain, because it has access to the service master key. In this case the data is cryptographically protected only against accidental media loss: a lost laptop with the database on it, or an improperly disposed HDD with database of backup files on it etc. For all other threats, the data is not cryptographically protected, is only protected by the access control: since the SQL Server itself can decrypt the data w/o needing a password from the user, any user with sufficient privileges can access the data w/o knowing the password. In other words, a compromised ASP application may allow access to the encrypted data. As a note, scenarios in which the root of encryption is some secret stored on the ASP web application itself are just a (badly designed) variation on this and do not change anything.

Scenario B: when the service requires the user to provide a password. The password must come from the end user. In a web application, the user must type the password in a form in the browser, it gets sent over SSL channel to the ASP application, which passes it to the SQL Session (again on an SSL channel) and SQL can now decrypt the data using this password. This scenario is typical for multi-tenant applications in which tenants provide the data access password. In this scenarios the data is cryptographically secured against unauthorized access, because the SQL Server itself, nor the intermediate ASP web application, simply do not know the password. Even for a syadmin user with all privileges it would be impossible to read the data. Data can be moved at will and remains just as unscrutable, as it can be, again, only be read by the end-user that has knowledge of the password at the root of the encryption chain. Note that any 'shortcut' deviation in this scenario in which the password is 'saved' somewhere intermediately and not provided by the end-user this scenario degrades immediately to the first Scenario A.

A mandatory read for you: Encryption Hierarchy.

From BOL:

any permission denial overrides all grants

The implication is that when you DENY permissions to an object those permissions override any GRANTed permissions applicable to that object.

Having said that, you don't need to explicitly deny "view definition" permissions on procedures and other code. If you don't grant VIEW DEFINITION the definitions are not visible, even if the code can be executed. Tables work slightly differently; you'll need to deny view definition to any tables the role has SELECT rights on.

You can test this using the code below.

Create a test database:

USE master;
GO
IF EXISTS (SELECT 1 FROM master.sys.databases d WHERE d.name = 'TestPerms_20160921')
BEGIN
    DROP DATABASE TestPerms_20160921;
END;
GO
CREATE DATABASE TestPerms_20160921;
GO
USE TestPerms_20160921;
GO

Create a table, and a stored proc:

IF OBJECT_ID('dbo.Test') IS NOT NULL
DROP TABLE dbo.Test;
CREATE TABLE dbo.Test
(
    TestID INT NOT NULL
);
GO
IF OBJECT_ID('dbo.TestProc') IS NOT NULL
DROP PROCEDURE dbo.TestProc;
GO
CREATE PROCEDURE dbo.TestProc
AS
BEGIN
    SELECT 1;
END
GO

Create a test user, and a test role:

IF EXISTS (
    SELECT 1 
    FROM sys.database_principals dp 
    WHERE dp.name = 'TestU'
    )
BEGIN
    DROP USER TestU;
END
CREATE USER TestU WITHOUT LOGIN;

IF EXISTS (
    SELECT * 
    FROM sys.database_principals dp 
    WHERE dp.name = 'dbtestrole'
    )
BEGIN
    DROP ROLE dbtestrole;
END
CREATE ROLE dbtestrole;

ALTER ROLE dbtestrole ADD MEMBER TestU;

Grant SELECT and EXECUTE to the test objects:

GRANT SELECT ON dbo.Test TO dbtestrole;
GRANT EXECUTE ON dbo.TestProc TO dbtestrole;

Impersonate the user so we can see the effect of the GRANT statements above:

EXECUTE AS USER = 'TestU';

/* column names and types **are** visible */    
SELECT s.name
    , t.name
    , c.name
    , ty.name
FROM sys.tables t
    INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
    INNER JOIN sys.columns c ON t.object_id = c.object_id
    INNER JOIN sys.types ty ON c.user_type_id = ty.user_type_id
WHERE s.name = 'dbo'
    AND t.name = 'Test'
ORDER BY s.name
    , t.name
    , c.name;

/* access to view the definition is denied */
EXEC sys.sp_helptext @objname = 'dbo.TestProc', @columnname = NULL;

/* we can run the proc! */
EXEC dbo.TestProc;

REVERT;

DENY VIEW DEFINITION on the table, and test again:

DENY VIEW DEFINITION ON dbo.Test TO dbtestrole;

EXECUTE AS USER = 'TestU';

/* no rows returned as we can no longer view the table definition */
SELECT s.name
    , t.name
    , c.name
    , ty.name
FROM sys.tables t
    INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
    INNER JOIN sys.columns c ON t.object_id = c.object_id
    INNER JOIN sys.types ty ON c.user_type_id = ty.user_type_id
WHERE s.name = 'dbo'
    AND t.name = 'Test'
ORDER BY s.name
    , t.name
    , c.name;

/* we can still run the proc */
EXEC dbo.TestProc;

/* we can still see the content of the table */
SELECT *
FROM dbo.Test;

REVERT;

Cleanup:

USE master;
GO
IF EXISTS (SELECT 1 FROM master.sys.databases d WHERE d.name = 'TestPerms_20160921')
DROP DATABASE TestPerms_20160921;