TeamCity – Upgrade to 7.1 & Migrate to SQL Server

Having gone through the process of upgrading a TeamCity server from 7.0.3 to 7.1 and then migrating to a SQL Server back end I thought I would highlight some of the problems and solutions I encountered.

First of all, this article helped me immensely in getting through the process. I followed the exact same process listed in the article to upgrade my TC server from 7.0.3 to 7.1 and then again when migrating to a SQL Server back end. The only notable difference was that I did not need to specify the SQL Server port number (1433) in the connectionUrl entry within the database.properties file. Also, before doing the upgrade to SQL Server make sure you can login to your new (empty) TeamCity database using a TCP/IP connection otherwise the maintainDB command will fail. You need to ensure that the SQL Browser service is running and then you can test a TCP connection either by;

  • Using the SqlCmd Utility and specifying the tcp protocol for the -S flag
  • Using SSMS and selecting the Options >> button and setting the Network Protocol under the Connection Properties tab

SSMS Options

The other part of the upgrade process that is worth a mention, for 7.1 at least, is the NTLM authentication. Make sure you read this article first, and complete the changes, before you complete the tasks listed in this article. Since we had made the switch a SQL Server back end I did come across one gotcha when trying to login to TeamCity after changing to NTLM authentication. As per the documentation “… on switching from one authentication to another you start with no users (and no administrator) and will be prompted for administrator account on first TeamCity start after the authentication change.” However, when I tried to create a new administrator account it would fail saying username or password is incorrect and I could not login to my new TeamCity server. To get around the problem I had to do the following;

  • Stop the TeamCity Server service.
  • Take a backup of my TeamCity database (just to be safe, but this is optional).
  • In SSMS open up the users table in the TeamCity database and look for the administrators entry in the table – this should be row #1.
  • In the auth_type column change the entry from DefaultLoginModule to NTDomainLoginModule.
  • Restart the TeamCity Server service

I was then able to login to the server as the administrator to ensure the settings were correct and new users to the system could automatically login to the TeamCity server.

Advertisements

Named Pipes Provider, error: 40 – Could not open a connection to SQL Server

Named Pipes Provider, error: 40 - Could not open a connection to SQL Server

This one was frustrating to resolve as there are a number of reasons this error could occur and lots of information out there about fixing the problem. My scenario was this; my IIS 7.5 web server was throwing this error when trying to connect to a SQL Server database on a different machine. I would get this error when calling the web application from a third machine. One of the frustrations was that if I made the same request from my browser on the actual web server I did not get any error, just the page I was expecting.

After searching the net, trying a number of the suggested solutions, triple checking my connectionString entry in the web.config I remembered that I hadn’t tried using the IP Address in the connectionString rather than the server name. Making this change solved my problem and my web application worked as expected from any machine. As it turns out, supplying the FQDN (Fully Qualified Domain Name) in the connectionString also worked. So now I had to try and figure out why the the web server was having trouble resolving to this particular database server?

Running the TRACRT command-line against my database server name gave me an unexpected response; the name of another server. So that got me thinking about whether the database server had been renamed recently – it was a VM that I was not responsible for maintaining. So I opened up SSMS and ran the command SELECT @@SERVERNAME AS ‘Server Name’. This gave me the name different name altogether! If I put this third server name into the connectionString that also worked, without needing to use the FQDN.

Some further investigation later revealed that the VM was a copy of another VM which was then renamed twice but no one had followed the procedure for renaming the SQL Server instance. This was what was contributing to the problems with the server name being resolved correctly.

SQL Server Management Studio & Windows Authentication across Domains

Getting access to a SQL Server in a different domain using SSMS.
When using SSMS to connect to a SQL Server in a different domain there is no way to type in an alternate DomainUsername through the UI. To connect to a SQL Server in a different domain using SSMS we need to do the following:
  • runas /netonly /user:[DOMAIN]\[USERNAME] “C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe”
  • You will then be promted for the users password which you should provide.
  • When SSMS starts up, type in the server name (or IP address) into the Connect To Server dialog and ensure the Authentication is set to Windows Authentication.
  • You do not need to worry about the Username/Password as this will be overridden. Press OK and you should be connected to the server in the different domain.

Creating an ASPState Database

Create an ASPState database .
 
To generate an ASPState database you need to use the ASPNET_REGSQL command line application within the relevant Framework directory under the Microsoft.NET directory. I am using Windows Server 2008 R2 (64-Bit) so it is located in the C:WindowsMicrosoft.NETFramework64v2.0.50727 directory. A quick tip when using VistaWindows7Windows2008 if you hold down the Shift key and right-click in a blank space in Windows Explorer you get the option to ‘Open Command Window here’ just to save a bit of time and typing.
 
An example of a simple installation of the ASPState database would be; aspnet_regsql – ssadd -sstype t -S <servername> -U <username> -P <password>
For more details on the full list of command line options take a look at http://msdn.microsoft.com/en-us/library/x28wfk74.aspx
 
 

Identifying SQL Server version information

How to identify SQL Server version information.
 
It couldn’t be much easier, really. Run the following T-SQL Script SELECT SERVERPROPERTY(‘productversion’), SERVERPROPERTY (‘productlevel’), SERVERPROPERTY (‘edition’) 
 
For more information on the results it returns have a look at the following KB article ; http://support.microsoft.com/kb/321185

SQL Server 2008 And Dundas Charts

Using Dundas Charts with SQL Server 2008 Reorting Services
Looking at SQL Server 2008 and in particular Reporting Services I have been interested in the new functionality that has been added and things that have changed since 2005. In particular I wanted to know which bits of the functionality within Dundas Charts that Microsoft had included with 2008. Fi you want a quick summary of whats not included take a look at http://support.microsoft.com/kb/956028. Basically if you have any existing Dundas charts in SSRS 2005 that use code then the 2008 charts that are shipped with SSRS will not be transferable.
Since Dundas did not sell everything to Microsoft the solution is to use Dundas’ own 2008 version of their charts. You can download evaluation edition, Version 2.2 is currently available at the time of writing and can be installed with SSRS 2008. Just to make things interesting, when I tried to install this version on a Windows 2008 Server running SQL Server 2008 the Wise Installation would hang whilst determining the disk space. To try and solve the problem I ran the installation again with logging using the following command line “C:JunkDCR22SQL10EVAL.exe” /L*v “C:JunkInstallLog.txt” /I and this time the installtion completed without any problems. I didnt even need to review the log file.
Having got the control installed successfully my next task is to import a SSRS 2005 chart that uses code and see if it runs with the next version on SSRS2008. Stay tuned for more details….