Encrypting connections in SQL Server 2005 & SQL Native Client with SSL

The following Link summarize settings for SQL server 2005 for protecting sensitive data in transit using SSL.


http://blogs.msdn.com/sql_protocols/archive/2005/11/10/491563.aspx

Code Module Signing in SQL Server 2005

SQL Server 2005 is that it provides the ability to digitally sign code modules to SP,triggers,functions with digital certificate. we sign the code with the private key contained within the certificate which means access to the tables are only provisioned to the certificates that have been used to sign the module. it is true for SPs too.

If it has an unbroken ownership chain, you carefully control which users get EXECUTE permission on the procedure, and you deny direct access to the underlying tables. But this doesn’t help in situations such as when the procedure has a broken ownership chain or executes dynamic SQL, requiring that the user executing the procedure have permissions to the underlying tables. Another way to achieve the same effect is to use EXECUTE AS, but this changes the security context under which the procedure executes. This may not be desirable, for example, if you need to record in the table the user who actually caused the procedure to run (short of requiring a user name as a parameter to the procedure).
Signing code modules has the additional benefit of protecting against unauthorized changes to the code module. Like other documents that are digitally signed, the certificate is invalidated when the code changes. The code doesn’t execute under the context of the certificate, so any objects that have their access provisioned to the certificate will not be accessible.
To do this, you create a certificate, associate it with a new user, and sign the procedure with the certificate. Grant this user whatever permissions are necessary to execute the stored procedure. In essence, this user is added to the security context of the stored procedure as a secondary identity. Then grant execute permissions to whatever users or roles need to execute the procedure. The following code shows these steps. Assume that you want to sign the mySchema.GetSecretStuff procedure, and that all of the referenced objects already exist in the database:

CREATE CERTIFICATE certCodeSign
ENCRYPTION BY PASSWORD = 'asdf@#$kdkf"
WITH SUBJECT = 'Codesigncertificate'
GO

-- Sign the stored procedure
ADD SIGNATURE TO mySchema.GetSecretStuff BY CERTIFICATE certCodeSign
WITH PASSWORD = 'asdf@#$kdkf'
GO

-- Map a user to the certificate
CREATE USER certUser FOR CERTIFICATE certCodeSign
GO

--Assign SELECT permissions to new certUser
GRANT SELECT ON SocialSecurity TO certUser
GO

-- Grant execute permission to the user who will run the code
GRANT EXECUTE ON mySchema.GetSecretStuff TO ProcedureUser
GO

Now only users explicitly granted EXECUTE permission on the stored procedure are able to access the table’s data.

Status of various SQL services after initial Installation

Lists the various SQL Server services, and the default state upon installation. For certain services, you can elect during installation to auto-start the service when Windows restarts, which affects the initial Windows service startup state

Service Startup---------->[AutoStart State] ---------->Default State

Analysis Services----------> Automatic---------------> Started

Full-Text Search----------> Automatic---------------> Stopped

Integration Services-------> Automatic---------------> Started

Notification Services-----> Unconfigured----------> Unconfigured

Reporting Services-------> Automatic---------------> Started

SQL Browser-------------> Disabled ---------------->Stopped

SQL Server--------------> Automatic---------------> Started

SQL Server Agent-------> Manual ------------------> Stopped

SQL Writer-------------> Disabled------------------> Stopped

SQL Server AD Helper -->Disabled----------> Stopped

Security Recommondation for enabling endpoint in SQL Server and not using IIS on SQL Box

Following are the recommendations when using Handler(ISAPI) to interact with the backend SQL, the same applies

We have to take care of following

 End Point Authentication(try to use INTEGRATED if the application is internal)
 Input validation
 No Dynamic SQL in SP’s, UDF etc.
 Low privileged account
 Information Disclosure-Error Handling
 If the Data transfer is classified as HBI then Communication Channel Encryption (Like SSL)

Exposing SQL Server over the Internet is not a good choice (even with tight security). Thus, the native HTTP access model is ideally suited for intranet types of applications

Secuity in SQL Server 2005 Analysis Services

The process of securing Microsoft SQL Server 2005 Analysis Services (SSAS) occurs at multiple levels. We have to make sure that each instance of analysis and data source that only intended users have correct permissions on cubes, dimensions,cells, and to prevent unauthorized users from maliciously compromising sensitive business information


The process of securing an instance of Analysis Services is described in the following steps.

Security Architecture

Configuring the Logon Account for Analysis Services

Securing an Analysis Services Instance

Configuring Access to Analysis Services




Following are key features


Windows Authentication
Analysis Services uses Microsoft Windows Authentication to authenticate user access. If the instance of Analysis Services is configured to permit anonymous access, Windows does not authenticate the user.

Authorization:-Analysis Services determines Authorization whether the user has permissions to view data, update data, view metadata, or perform administrative tasks.By default, Analysis Services does not let a user connect if that user does not have some type of permissions within the instance of Analysis Services. Authorization continues for stored procedures, Data Mining Extensions statements, Multidimensional Expressions queries, or Analysis Management Objects commands

Stored Procedure Security
Permission sets that are used to run stored procedures:
Safe

With the Safe permission set, a stored procedure cannot access the protected resources in the Microsoft .NET Framework. This permission set only allows for computations. This is the safest permission set; information does not leak outside Analysis Services, permissions cannot be elevated, and the risk of data tampering attacks is minimized.
External Access With the External Access permission set, a stored procedure can access external resources by using managed code. Setting a stored procedure to this permission set will not cause programming errors that could lead to server instability. However, this permission set may result in information leaking outside the server, and the possibility of an elevation in permission and data tampering attacks.
Unrestricted With the Unrestricted permission set, a stored procedure can access external resources by using any code. With this permission set, there are no security or reliability guarantees for stored procedures.



Encryption
Analysis Services, requires that all communication between clients and the instance of Analysis Services be encrypted.It is By default

Off By Default
It is Microsoft slogan secure by default. Therefore, features that might compromise security are disabled by default. The following features are disabled by default, and must specifically be enabled if you want to use them:

1. HTTP Connectivity
   
2. Stored Procedures
   
3. Remote Partitions
   
4. Linked Objects (To)
   
5. Linked Objects (From)
   
6. Data Mining Aggregator
   
7. Client Protection Level
   
8. Web Protection Level
   
9. Required Client Authentication
   
10. Active Directory Integration
    
11. Ad Hoc OpenRowset Queries
    
12. 8.0 Client Connectivity
    
13. Crash Reports