How to SSL enable SQL 2005 Reporting Services

This assumes you have received the required CERT to enable SSL on IIS.

1. Install SSL cert to IIS and enable SSL and required 128-bit.
2. Open Reporting Services Configuratioin tool to configure the Report Server Virtual Directory.
3. Select Require Secure Socket Layer (SSL) connection checkbox.
4. Select level 3.
5. Enter certificate name which is (usually) the DNS of the webserver.
6. Click Apply.
7. Using Notepad, edit RSReportServer.config and modify the UrlRoot to use "https:" and the full DNS (certificatename) of the webserver.
   https://certificatename/reportservervirtualdirectoryname

Reference: http://msdn.microsoft.com/en-us/library/ms345223(SQL.90).aspx

Netbackup Restore Jobs

I was trying to do a SQL database restore job and in the Netbackup (NBU) Admin Console, the job showed that it successfully completed. However, on the SQL agent client console the job status showed that it failed.

The solution was that the MAXTRANSFERSIZE had one value "0" in the backup script and a different value "6" in the restore move script. Once I changed the "6" to "0", the job worked.

For more info on this, see Netbackup SQL Administrator's Guide at http://support.veritas.com/docs/290212

Reference: http://seer.entsupport.symantec.com/docs/311524.htm

SQL Agent Proxy Accounts

In SQL 2000, you could use a single windows account to be the proxy for all jobs that needed to run Sysadmin level tasks. New in SQL 2005 is that you can define multiple proxies for the non-Sysadmin.

To create:

1. Create or use a Windows login (local or domain) to be the proxy that will run the job.
2. Give that account "Log on as a batch job" right in the OS
3. Create SQL login for Windows login and give it SQLAgentUserRole in msdb.
4. Create the credential using SQL 2005 Management Studio (SSMS) to reference this account.
5. Create Proxy using SSMS and give it access to desired subsystem(s): ActiveX Script, Operating System, Replication Distributor, Replication Merge, Replication Queue Reader, Replication Snapshot, Replication Transaction-Log Reader, Analysis Services Command, Analysis Services Query, SSIS Package Execution
6. Job owner--->Proxy--->Credential--->Windows Login--->runs the job.

Reference: http://msdn.microsoft.com/en-us/library/ms189064(SQL.90).aspx

Configuring SSIS

SSIS is not cluster-aware so installing to a SQL 2005 cluster is problematic. I'm running an Active/Active, two instance, two physical server Windows 2003 cluster so I installed SSIS on both nodes and configured to reference MSDB on both virtual server\instances. I did not create anything within cluster groups for SSIS.

Reference: http://msdn.microsoft.com/en-us/library/ms137789(SQL.90).aspx

What's new in SQL 2005

Old, but good...

Reference: http://msdn.microsoft.com/en-us/library/cc966397(printer).aspx

How to transfer SSL certificates between IIS6

Export the server certificate

1. Run MMC
2. Add "Certificates" snap-in for "Computer Account"
3. Select "Local Computer".
4. Close add snap-in dialogs.
5. Open Certificates tree to Personal>Certificates.
6. Right-click on certificate to be exported and select "All tasks", "Export".
7. Specify "Yes" to export the private key.
8. Include all certificates in the certification path and click "Next".
9. Create a password and click "Next".
10. Save, next, then Finish.

Import certificate and private key server

1. Run MMC
2. Add "Certificates" snap-in for "Computer Account"
3. Select "Local Computer".
4. Close add snap-in dialogs.
5. Open Certificates tree to Personal>Certificates.
6. Right-click on >Certificates and select "All Tasks", "Import".
7. Select PFX file you created in previous section and be sure to "Mark this key as exportable".
8. Enter password created in previous section.
9. Confirm that "Personal" is selected as the Certificate Store and click Next.
10. Click Finish to complete.

Assign certificate to website

1. Open Internet Service Manager.
2. Right-click on website you want to install SSL cert.
3. Right-click website and select "Properties".
4. Select "Directory Security" tab.
5. Select "Server Certificate", then Next.
6. Select "Assign an existing certificate", then Next.
7. Highlight imported server certificate.
8. Assign SSL port, then Next.
9. Review summary of changes, then click Next and Finish.

Reference: http://www.microsoft.com/technet/prodtechnol/WindowsServer2003/Library/IIS/2cfeeba2-511f-47e8-913c-f196b74e6a44.mspx?pf=true

Reporting Services SQL 2005 Performance tuning

This is a nice article about this topic.

Reference: http://technet.microsoft.com/en-us/library/cc966418(printer).aspx