locked
Using Named SQL Instance for Reporting Database? RRS feed

  • Question

  • Is it possible to give an SQL Instance name for the Reporting Database server?

    As far as we know, the Reporting Databse Server must not be an Alias. When a client opens a reporting document, the Alias cannot be found. So now we try to change the Reporting Database Server location to the SQL Server Named Instance, but this does not work.

    This is our configuration:

    SPSQL is an Alias pointing to CSQL001\SP.

    The following error occur when we try to connect to the SQL Named Instance:



    Provisioning 'projecten': Failed to connect to database server CSQL001\SP with the exception System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)    
     at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)    
     at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)    
     at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject)    
     at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)    
     at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)    
     at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)    
     at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)    
     at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)    
     at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)    
     at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)    
     at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)    
     at System.Data.SqlClient.SqlConnection.Open()    
     at Microsoft.Office.Project.Server.Data.SqlSession.OpenConnection()    
     at Microsoft.Office.Project.Server.Data.SqlSession.ExecuteReader(SqlCommand command, CommandBehavior behavior, SqlQueryData monitoringData, Boolean retryForDeadLock)    
     at Microsoft.Office.Project.Server.Data.SqlSession.ExecuteReader(SqlCommand command, Boolean retryForDeadLock)    
     at Microsoft.Office.Project.Server.Data.SqlServerManager.GetVersion()    
     at Microsoft.Office.Project.Server.Administration.PsiServiceApplication.EnsureDatabases(ProjectProvisionSettings provset, SPSite pwaSite, String adminName, String adminEmail, ProjectDatabaseStateType& originalDatabaseState, Guid& adminGuid).

    Is there another way of givin in a Named Instance?

    We tried:
    CSQL001\SP
    CSQL001/SP
    CSQL001:1435

    In our testing environment, where we do not have a named instance, we can connect to the SQL server and reporting is working fine.

    Wednesday, February 22, 2012 12:27 PM

Answers

  • We are al little further.

    The Alias connects to the instance using its portnumber. When we try to connect by name, it does not work.

    It seems UDP port 1434 is closed to the server and that is the reason the Instance Name cannot be resolved. Now we are waiting for our network specialist to open the port.

    We did not find a way connecting to the instance, in Sharepoint, using the portnumber. Is there?
    Also tried to use:
    CSQL001,1435 (but a comma is forbidden)

    Wednesday, February 22, 2012 2:11 PM
  • You will have similiar problems with the Analysis Services if you use these. Make sure the right ports are open (1434
    and 2382) and the SQL Browser service is running.

    Ben Howard [MVP] blog | web

    Thursday, February 23, 2012 9:50 PM

All replies

  • We are al little further.

    The Alias connects to the instance using its portnumber. When we try to connect by name, it does not work.

    It seems UDP port 1434 is closed to the server and that is the reason the Instance Name cannot be resolved. Now we are waiting for our network specialist to open the port.

    We did not find a way connecting to the instance, in Sharepoint, using the portnumber. Is there?
    Also tried to use:
    CSQL001,1435 (but a comma is forbidden)

    Wednesday, February 22, 2012 2:11 PM
  • This was it!

    Sharepoint/Project could not resolve the Named Instance because of the blocked SQL UDP port (1434).
    Now the port to the Sharepoint/Project server is open, we can make connection to our Named Instance: CSQL001\SP

    Thursday, February 23, 2012 10:59 AM
  • You will have similiar problems with the Analysis Services if you use these. Make sure the right ports are open (1434
    and 2382) and the SQL Browser service is running.

    Ben Howard [MVP] blog | web

    Thursday, February 23, 2012 9:50 PM
  • You will have similiar problems with the Analysis Services if you use these. Make sure the right ports are open (1434
    and 2382) and the SQL Browser service is running.

    Ben Howard [MVP] blog | web

    Hi Ben,

    Thank you for your information.

    We requested to open port 1434 and 2382 also, but first we test with only 2383 open.
    This is because of the following sittuation:

    The Analysis server and SQL server are in the same network. These two servers should have no problems talking to eachother.
    The Project server is in a different network. That is why we had to open 1434 to the SQL server for using named instances.

    For analysis server we do not use named instances (could be I'm totally wrong in my understanding). As far as we know, only port 2383 sould be open for Analysis using the default instance.

    Source: http://msdn.microsoft.com/en-us/library/cc646023.aspx

    - 2383 = Analysis TCP port for the default instance
    - 2382 = Analysis TCP port only needed for an Analysis Service named instance
    - 1434 = SQL UDP port for Server Browser service

    As I mentionned... maybe I'm totally wrong. For me it is still a way to learn understand it :)

    When I'm wrong... please explain :)

    Friday, February 24, 2012 8:01 AM
  • building the cubes will be no problem, because, as you say, the DB and AS server are on the same network.  However, creating AS reports requires direct access to the AS server from Excel, therefore these ports will need to be open between the client PC and the AS server.  For users viewing the reports via Excel Services, there are no additional requirements.

    Ben Howard [MVP] blog | web

    Friday, February 24, 2012 10:08 AM
  • Hi Ben,

    The cubes are build! After opening port 2383 it went all fine.

    We will keep your sugestion for the client pc's in mind.

    Thanks again.

    Friday, February 24, 2012 11:55 AM