SocketException when upgrading Reporting Services from SQL 2008/R2 to SQL 2012

[mssql-support]

First published on MSDN on Jul 25, 2012
I ran into an interesting case yesterday that brought back some memories.  The customer had a SQL 2008 R2 instance of Reporting Services that they were upgrading to SQL 2012.  They didn’t get far into the upgrade when they hit the following error which was present in the Setup Logs:

(16) 2012-07-24 05:52:09 Slp: Initializing rule      : Valid DSN
(16) 2012-07-24 05:52:09 Slp: Rule is will be executed  : True
(16) 2012-07-24 05:52:09 Slp: Init rule target object: Microsoft.SqlServer.Configuration.RSExtension.DsnUpgradeBlockers
(16) 2012-07-24 05:52:10 RS: Error validating Report Server database version: System.Net.Sockets.SocketException: The requested name is valid, but no data of the requested type was found
at System.Net.Dns.GetAddrInfo(String name)
at System.Net.Dns.InternalGetHostByName(String hostName, Boolean includeIPv6)
at System.Net.Dns.GetHostEntry(String hostNameOrAddress)
at Microsoft.SqlServer.Configuration.RSExtension.DsnUpgradeBlockers.IsLocalDbServer(String dbServer)
at Microsoft.SqlServer.Configuration.RSExtension.DsnUpgradeBlockers.SetValidDatabaseVersion(DSN dsn)
(16) 2012-07-24 05:52:10 Slp: Evaluating rule        : RS_ValidDSN
(16) 2012-07-24 05:52:10 Slp: Rule running on machine: MATLKPACSAPP001
(16) 2012-07-24 05:52:10 Slp: Rule evaluation done   : Succeeded
(16) 2012-07-24 05:52:10 Slp: Rule evaluation message: The Report Server has a valid DSN.
(16) 2012-07-24 05:52:10 Slp: Send result to channel : RulesEngineNotificationChannel
(16) 2012-07-24 05:52:10 Slp: Initializing rule      : Valid Database compatibility level and successful connection
(16) 2012-07-24 05:52:10 Slp: Rule is will be executed  : True
(16) 2012-07-24 05:52:10 Slp: Init rule target object: Microsoft.SqlServer.Configuration.RSExtension.DsnUpgradeBlockers
(16) 2012-07-24 05:52:10 RS: Error validating Report Server database version: System.Net.Sockets.SocketException: The requested name is valid, but no data of the requested type was found
at System.Net.Dns.GetAddrInfo(String name)
at System.Net.Dns.InternalGetHostByName(String hostName, Boolean includeIPv6)
at System.Net.Dns.GetHostEntry(String hostNameOrAddress)
at Microsoft.SqlServer.Configuration.RSExtension.DsnUpgradeBlockers.IsLocalDbServer(String dbServer)
at Microsoft.SqlServer.Configuration.RSExtension.DsnUpgradeBlockers.SetValidDatabaseVersion(DSN dsn)
(16) 2012-07-24 05:52:10 Slp: Evaluating rule        : RS_ValidDatabaseVersion
(16) 2012-07-24 05:52:10 Slp: Rule running on machine: MATLKPACSAPP001
(16) 2012-07-24 05:52:10 Slp: Rule evaluation done   : Failed
(16) 2012-07-24 05:52:10 Slp: Rule evaluation message: The report server database is not a supported compatibility level or a connection cannot be established. Use Reporting Services Configuration Manager to verify the report server configuration and SQL Server management tools to verify the compatibility level.

The minute I saw the SocketException and the Dns.GetAddrInfo, I remembered a blog post I had created a while back – 2009 to be exact.  This was here .  Although this one was a little different.  Also, of note, the original item I had filed in 2009 was related to the Reporting Services Configuration manager and was subsequently fixed with 2008 SP3 as well as SQL 2008 R2 and SQL 2012.  Which actually explains how the customer got into this situation to begin with, but I’ll come back to that.

The original problem was when you tried to use a server name in the format of SERVER,PORT (i.e. myserver,1433).  For a normal SQL connection using SQL Native Client or our ODBC/OLE DB providers, this would work.  However, on the Configuration Manager side of the house, we were only parsing for a “\” which is the usual character for when we have a named instance.  There are scenarios though where people don’t want to specify the Named Instance name and just want to use a port.  In the previous issue, it was due to a firewall restriction not wanting to open up UDP 1434 to allow for SQL Browser to work, which is needed for Named Instance resolution to a port.  The fix to the 2009 post was that code was added to also parse for the “,” as well as the “\”.

One thing we will notice is that the call stack in the exception above, differs from the call stack in the original post.

2009 Post:

ReportServicesConfigUI.RSDatabase.IsLocalDbServer(String dbServer)

This issue:

Microsoft.SqlServer.Configuration.RSExtension.DsnUpgradeBlockers.IsLocalDbServer(String dbServer)

So, the correction happened when we were down the ReportServicesConfigUI code path which is specific to the Reporting Services Configuration Manager.  We have a second code path which still had the original issue, which is invoked when we perform a Setup/Upgrade.

How did this even happen?

Now I come back to how we got here in the first place.  Well, we corrected the Configuration Manager issue which means that within Configuration Manager you can set your server name to the format of SERVER,PORT and it will work.  Think about this scenario.  The Customer installed SQL 2008 R2 Reporting Services in Files Only mode indicating that they want to configure it later.  After setup is complete, they open up Reporting Services Configuration Manager and go through the motion to Create a new Database for Reporting Services and specify the server name in the SERVER,PORT format.  In the issue I saw, they had changed the port of their default instance to 14330 so they used something like myserver,14330.  This worked. Then they go to upgrade to SQL 2012 and hit the error above.

How do we get around it?

So, while we know this issue can occur, how do we get around it so that we can do a successful upgrade?  The good thing here is that Setup didn’t get very far, so we are still with our SQL 2008 R2 instance.

NOTE:  Before doing anything, take a backup of the Reporting Services Catalog DB (by default – ReportServer) and a backup of your Encryption Keys from the Reporting Services Configuration Manager.

One of the easier things we could do is to do the following for a native instance:

1. Use Reporting Services Configuration Manager to create a new Database on a different instance where we can use the traditional Server naming format without the port information.
2. Perform the Upgrade
3. Then use the Reporting Services Configuration manager to point back to the original database at which point it should be upgraded.

If you don’t have access to another SQL Instance, it may involve using the SERVER\INSTANCE format, or if you are in the case of the customer who changed the default port, change it back to the default port to perform the upgrade and then change it back.  Although, that could potentially affect a lot of other items, so you want to make sure that you plan that accordingly as to not impact other areas of your business.

Adam W. Saxton | Microsoft Escalation Services
https://twitter.com/awsaxton