Tech Notes on SQL 2005 and VS 2005 Installation

The following paper represents experience gained through the installation of the beta SQL Server, Visual Studio and Reporting Services by myself and six other team members on variously configured machines and operating systems. The intent of this information is to help prevent others from haivng trouble when performing this type of install.

• TheApril CTP is the last co-joined build until at least August and

  possibly not until September. Even though newer files are available do not use them until or unless you understand the implications.

• We are using the files on the server named

  “SQLServer2005CTP-April2005-Developer” and “Visual

  Studio 2005 Team Suite Beta 2”.

• SQL Server 2005 should be installed prior to installing Visual Studio 2005.

Which Accounts? Best Practices Summary

We use an SSL Certificate for SQL Server 2005 and Reporting Services and the following accounts:

• Run the Windows Service under NT AUTHORITY\SYSTEM (XP & 2000)
• Run the Windows Service under NT AUTHORITY\NETWORK SERVICE (2003)
• Use a Domain Account for the services to access SQL Report Database
• Internet Information Services (IIS) must be preinstalled, set to automatic and running prior to installing the reporting services portion of Visual Studio 2005.  It will be grayed out and not selectable if IIS is not present.

IIS Connections in XP limited to 10 - increase to 40 here

A single web browser session will take two and may take as many as four connections.  When you exceed ten you will get a 403.9 error message.  This can be increased to 40; but not beyond.  If you go higher then 40 it will slam you back to ten.

From a command prompt (DOS Window) execute the following script:

CScript.Exe C:\Inetpub\AdminScripts\adsutil.vbs set w3svc/MaxConnections 40

Obviously, adjust your path accordingly if IIS was installed in a different location.

Notes (continued)

• We have a client application that uses SQL Server 2000 and the named pipes protocol, and Named Pipes appears to get in the way of the 2005 installation. Since I did not have the application installed I did not get this error. My experience with Named Pipes in both 2000 and SQL Server7 was to deselect them on installation (i.e., not to use them).  They cannot be deselected in 2005 until the product is installed.  They are turned on by default.
• One person was successful in removing the 2000 client after the Named Pipes failure and continuing where he left off (i.e., without removing everything and starting over).
• Delete the SQL Server 2000 client and the related registry key.  Using regedit delete the key, as follows:

  HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer

Latest Research & Thoughts on SQL Server Installation

Recommendations based on information at Microsoft Technical Resource

During SQL Server installation do not select Service Account | Service Settings | “Use the Local System account” for running the SQL services. A Domain User account should be used: an account with no special privileges beyond being a “user”.

Since the machine I am using is not part of a domain, and since my local account has administrator privileges, I have created the following local user account:

SQLServiceUser

This account has no special privileges or access and was created so the user may not change the password and the password does not expire. Ideally a similar domain account will be created and so named and become a corporate standard for SQL Server installation.

Using an account with administrative access is dangerous if the SQL server is attacked. As a generic user account with no special access, the attack would be minimized.

The strong password used for this account is “<omitted>”. Obviously, not a good idea to put into a published or distributed document; but this is a test. Port 1433 will also be disabled in my firewall – so don’t even think about it.

The Authentication Mode should be selected as “Windows” ONLY. The Mixed Mode is truly easier to use; but there is a reason that more secure modes are harder to use. In most of the environments where I have used SQL Server it was not the primary concern, and security was not given too much thought.

Security will be dealt with carefully in Reporting Services; but if the underlying database is improperly installed it might be a waste of time.

Note: On 2000, deselect named pipes. 2005 does not give you the option during installation.

The SQL Server Agent should run under this same SQLServiceUser account. There are exceptions when the SQL Agent needs administrative privilege; but we are not one of those exceptions to my knowledge.

After SQL Server is installed and patched, the IIS Took Kit should be used to install a test certificate into IIS. Then you should install Reporting Services and patch appropriately.

Note: Since you have selected Windows Authentication you were not prompted to enter a password for the “sa” account. During the patching you will be prompted to enter one or allowed to leave it blank. DO NOT LEAVE IT BLANK! Enter a strong password. For the test database in my test server the password is “<omitted>”.

Notes (continued)

• When selecting products to install open the “advanced” button and make sure you are getting “Books Online”.  This is off by default.  I did not need the Crystal Web interface which is also deselected; but selecting everything here is

  not a terrible thing.

• The Framework 2.0 beta software install fails with a message the “the file named ‘none’ does not exist”.  If you allow it to continue it will appear to install; but the failure will show up later in the process – and removing everything and starting over could be necessary.

• You may only see the message “SQL Server failed to obtain system account information for the report server database account… a file is missing or corrupt.  To proceed, reinstall the .NET Framework, and then run setup again.”  This was caused by the file named “none”.

• When the failure occurs related to Framework 2.0 (above) you will be on a screen that gives choices of “retry, ignore, or cancel”.  You must cancel.  Use add/remove programs in the control panel and select remove; but when it comes up, do not select uninstall,

  select repair.

• Framework 1.1 must remain installed.  If you uninstall it, reinstall with a distribution pack, or through the Windows Update site.  After installing there is a patch, and after the patch, there is a patch to the patch (i.e., it is a three-step reinstallation process. Get it all.)

• Initially there were four errors (five if you count the one that happened twice) caused by an extremely long path from the fileserver copy of the software image.  This was fixed and should not happen again.  (In fact, it has not re-occurred since fixed).

• After installing SQL Server 2005 make sure that the various services are set to automatic and started.  It would not hurt to reboot and check that they started.  The Surface Area Configuration (SAC, or SQLSAC) program can be used for setting these as can the services

  interface.

• There are four errors in the Application Event log that occur twice after a reboot.  They will also occur repeatedly over the course of the day.  They are error event “113” and generically state “the report server cannot create a performance counter.”  These deal with “total cache hits, hits/second, total cache misses, and misses per second”.

Some research indicated this is caused by IIS and TCP/IP – and the only tentative solution I have found is uninstall/reinstall.  Not going to do it!  Do not yet understand the repercussion of this failure.

• There are “features” in SQL 2005 that are turned off by default.  They can be turned on through the SAC utility.  While I understand the advantage of disabling features and making the software footprint smaller – beta software is buggy enough not to have to distinguish between not turned on and buggy.  I turned them all on.  Unused features should be turned off before deployment so that the application will not have to be

  deployed with access that is not required.

For instance, the Common Language Runtime (CLR) should probably not be deployed, at least for most users; but we will want to be able to use it.  Turn it on.

• In the SQL Server Management Studio there is a bug that will not let you attach to both the Integration Services and Reporting Services at the same time.  The services are running, you just can not connect to them both at the same time.  This is probably a GUI issue, and hopefully will not happen in the final product; but beware if you programmatically try to attach to both services in your code.

• DTS has been replaced with SSIS. SSIS is no longer a free client-side option; but a registered component and delivered with SQL Server 2005 license. Development is not done in SQL Server however; it is done in the Business Intelligence interface (Visual Studio 2005). The DTS tabs in SQL Server 2005 are for migration of existing packages only. There is a long list of exceptions as to what will migrate.

• The name change apparently occurred during development so there is still documentation, examples, and possibly tools that use the wrong reference. Do not be confused into believing that this was simply a name change. It is a whole new product.

• Once SQL Server 2005 is installed and operating you may proceed to Visual Studio 2005.  Select the “custom” installation choice and deselect the last entry in the list.  This is to say, deselect the Express Edition of SQL Server so it does not

  install.

• Two icons are created for Visual Studio and the Business Intelligence Development Studio.  They both start Visual Studio – and I am not totally sure why this should be this way.  There don’t appear to be any differences in these configurations.

• There is also a Visual Studio “shell” that gets downloaded when a user does not have the full Visual Studio installed, so some installation may see this shell, or an icon for this shell, as well.

• The help tables are virtually empty in the beta product.  Hopefully you downloaded Books Online.

Reporting Services - Extract and backup symmetric encryption key

I have seen various recommendations to extract and backup the symmetric encryption key; but it took a bit of a search to find out the technique.  The utility to use is named rskeymgmt.  This should be run as part of every installation of reporting services and should be incorporated into the installation instructions when they are written.

The symmetric encryption key is an integral part of storing encrypted data.  It is used to encrypt data in the report server database and/or catalog.  The utility captures and stores the key in a file that can be saved on removable media and taken offsite.

You will need the key if the account service changes as insignificantly as a change of the password, or if you want to connect a new report server to an existing report server database instance.  Without this key you may have no choice but to delete all encrypted data in your catalog including data source and user information.

If, at some point, the encryption key for the report server instance is different from the key used to store encrypted content in the database (i.e. a new report server installation, but an existing catalog), you report server will not function properly. So, bottom line, store your encryption key on a floppy disk for safe keeping using rskeymgmt.

RSKeyMgmt –e –p<password> -fc:\backupkey\<server name>.snk
RSKeyMgmt { -? }
-e
-a
-r
-d
-f
file
-p
password
-t

Prior to using the utility make the subdirectory c:\backupkey

If I may suggest a password, use a password that will not be forgotten; but still “strong”. There is no space between the command line flag and the password or directory name. A copy should be stored on removable media (and potentially offsite).

IIS Tool Kit

Please be aware of the availability of the Microsoft IIS Tool Kit when it comes time to re-install the newest co-joined release of SQL 2005 and VS/Reporting Services. Reporting Services should be installed using SSL. The tool kit will allow you to quickly generate a test SSL certificate and install it directly into IIS. This has been tested successfully on both XP and AS 2003.

SQL Server should also be installed using SSL and a certificate; but that has not been covered and will be included later. It does not seem to make much sense to install a certificate in IIS and not in SQL Server.

Reporting Services changes if SSL turned on AFTER installation

The following changes are required to Reporting Services if you add a self-certification after the initial installation:

Edit RSReportServer.config file

Change UrlRoot from http://... to https://....

Change “SecureConnectionLevel” value of 0 to 2

2 is secure – use SSL for rendering; but don’t insist on it for all SOAP calls

3 is most secure – use SSL for absolutely everything

These should be set properly if certificate is installed in IIS before installation of Reporting Services. Additionally, if you created shortcuts for ReportManager and ReportServer you should change the references to https:// (vs. http://)

SSL Bug Fix 2005 ONLY

Okay, perhaps that is not completely true. My 2005 configuration is in a domain. My 2003 configuration is not, it is only a workgroup machine. Therefore I cannot attest to this being true in 2003 Reporting Services; but the solution was gleaned working between the two.

Edit ....\Reporting Services\ReportManager\RSWebApplication

Read on if the following two lines exist in your file:

<ReportServerUrl></ReportServerUrl>

<ReportServerVirtualDirectory>//<localhost>/ReportServer</ReportServerVirtualDirectory>

Correct the first line to include https://<localhost>/ReportServer, as follows

<ReportServerUrl>https://<localhost>/ReportServer</ReportServerUrl>

Remove the second line.

If you leave both lines then SSL will not work properly on the site settings page, my subscriptions, and home, to name three. You will also get the following message when you try to create a data source:

The underlying connection was closed: Could not establish secure channel for SSL/TLS

If you simply edit the URL, you will get an error message that the Report Server URL is not a valid configuration setting. If you remove the line completely, SSL will not work.

Final Tweaks required to IIS to make SSL work properly

Default Web Site Properties

Directory Security Tab

Edit Anonymous Access and authentication control

Uncheck Anonymous access

Uncheck Basic authorization (password is passed in clear text)

Edit Secure communications

Require Secure Channel (SSL) 128 bit

Remaining Issues with SSL Configuration

The help tab in the Report Manager still points to an http: page. If you edit the URL in the browser address to https: the help is available. This could be an easy fix; but this is beta software and not worth any effort at this time.

There is a similar problem in Visual Studio when you attempt to deploy. You get the same error message as above about a secure channel. Since our organization uses external configuration management (CM) tools to create builds we do not care whether this features works or not. In fact, we would prefer the choice not to be available. Our builds are done daily and it is the responsibility of each developer to make sure their work is compatible with the project team’s code. This way the burden does not fall on one individual, usually in CM.

Bugs personally encountered in April CTP:

SQL Server 2005

• Report Service|properties|execution

  • Fails with “value of 0 is not valid… system.windows.forms”

• Maintenance plan new and/or wizard

  • Fails with OLEDB error (DBNETLIB) SSL Security Error…
  • Although SSL is not installed

• Cannot connect to integration service and reporting service in GUI at same time

• Both are functioning – appears to be GUI bug only

Visual Studio 2005

• Names of solution and project do not appear properly in GUI

  • Appear to be proper in Windows Explorer; however, correcting in GUI and changing to display properly also chances the resultant directory structure in file system.

    • <paper being written on subject: file locations>

• Build tab has duplicate choices: Build, Rebuild, Clean | Build, Rebuild, Clean

  • So does “recent project” for that matter:

    • Open: Project/So… | Project/S…

    • Create: Project… | Project…

• File | Add | New Project does not work

  • The OK button is grayed and not accessible

  • File | New | Project works just fine

• File path specification is quickly exceeded particularly when using

  default names

  • This

    was in SSIS template – starting in My Documents and going

    down

• Issues

  with “property expressions” in beta 2 – required

  to read variable file name from operating system into SSIS

• Cannot

  create subscription because credentials are not stored…

  • Even

    with “credentials not required” will not allow

    subscription creation

• Data

  store does not show up in Report Manager Report Builder even after

  deploying product – shows in directory structure; but not pick

  list of the Report Builder product

Conclusion

These are notes made for installation in a corporate development environment. They are not intended nor should they be construed as exhaustive and complete, but hopefully they will help others from experiencing similar difficulties.