Configure Reporting Services Database on a SQL Server with non-default port

I know this is “so last year” when talking about SQL Server 2008 Reporting Services R1 but I figured I’d talk about it anyway, as it may apply to R2 as well. I haven’t checked so please feel free to leave a comment if you can confirm one way or the other.

I am also aware that it has been a long time since we discovered this problem and that there is now another more elegant solution to the problem as well. However, I figured I would provide the information on how we solved this same issue for reference.

So, given a SQL Server instance where the SQL Browser service is disabled (or cannot be reached) and/or SQL Server is using a non-default port number, when attempting to generate the “rights” script whilst configuring Reporting Services you get the following error:

System.Net.Sockets.SocketException: No such host is known

    at System.Net.Dns.GetAddrInfo(String name)

    at System.Net.Dns.InternalGetHostByName(String hostName, Boolean includeIPv6)

    at System.Net.Dns.GetHostEntry(String hostNameOrAddress)

    at ReportServicesConfigUI.RSDatabase.IsLocalDbServer(String dbServer)

    at ReportServicesConfigUI.RSDatabase.GrantRSConnectionRights()

    at ReportServicesConfigUI.RSDatabase.CreateNewDatabase()

In our case, our UAT environment has the SQL Browser service disabled and we do not use the default port for SQL Server, both for security reasons I suspect. As such we need to specify our SQL Server as “servername.domain,port” which in the general case is fine. Even when creating the actual databases whilst configuring Reporting Services this is fine. However, it would appear that when generating the rights script, this form of connection string is not parsed into the individual server name and port number components. Really bizarre when one part of the product does and the other does not, especially when it is in the same part of the configuration!

For further confirmation that the connection string is not parsed, see:

http://blogs.msdn.com/b/psssql/archive/2009/03/05/socketexception-when-creating-a-new-report-server-database.aspx

So after much Googling at the time, we discovered the link above, as well as the following one, which at the time did not include a solution.

http://blogs.msdn.com/b/sameersurve/archive/2009/06/15/no-such-host-is-known-error-when-configuring-ssrs-2008.aspx

However, from both of those links we did get enough information in order to determine what we needed to do. Still, I’m not sure why specifying the instance name as well as the port number works (as detailed in the second of those links) over just the port number as that’s a bit weird, but if it works, hey why not use it. Note: I haven’t tested that the instance name and port number does actually work.

In our case though, we went with the *nix philosophy that you can always do more with the command line tools than you can with the GUI. So we did that. Essentially we used the GUI to configure Reporting Services as far as it would let us, including setting up the databases and allowing it to fail when it tried to configure the “rights scripts”. We then switched to using the command line tools by connecting as the relevant user and then ran the following:

rsconfig -c -m <SERVER_NAME> -s <DATABASE_NAME,PORT>  -d reportserver -a Windows

Where <RS_SERVER_NAME> is the server name for the Reporting Services server and <DATABASE_NAME,PORT> is the database name and port number for the SQL Server where we were trying to install the RS databases.

After running the command, we restarted Reporting Services, went back into the configuration tool to apply the changes there and continued.

Clearly this is a bit of a hack, at best, and most likely not supported but in our case it worked and we were up and running after a couple of hours of head scratching and bemoaning the insanity that is Reporting Services.