none
SQL Server on custom port RRS feed

  • Question

  • Our business policy is to not run SQL on the default port. I was able to get Project Server to install and run successfully on the non-default port, but building the OLAP databases (Analysis Services) does not connect and fails.

    When we set SQL back to the default port, we are successful. Is there any guidance to connecting Project Server to SQL via a different port than the default? I have created a DSN which is successful except for the building of the OLAP database.

    Help!

    Thanks,


    VergeOn - Callistus Lucien

    Thursday, January 17, 2013 1:11 AM

Answers

  • Finally! So if anyone is interested...

    First thank you one and all for your responses and help. I confirmed a number of your configuration suggestions which led me to this solution...

    • The tech guys have configured the network to use the FQDN to reference the SQL Server.
    • A number of processes and instances seems to want to use the server name.
    • I had created an Alias using the FQDN to be consistent with the tech guys' request.
    • I had success renaming the Alias to the server name (or a shorter name?!) pointing to the FQDN.
    • Reconfigured SharePoint and re-provisioned PWA to use the new Alias

    Eureka! The cube was successfully built. I am hoping this resolves all other related issues.

    Again thank you all,
    Callistus


    VergeOn - Callistus Lucien


    Tuesday, February 19, 2013 8:56 PM

All replies

  • I haven't seen any documentation for using different ports, but that doesn't mean they don't exist.

    The web.config for each site contains a lot of string connection information.  I would suggest looking in there and see if you can find a connection string to SSAS.

    Cheers!


    Michael Wharton, MVP, MBA, PMP, MCT, MCTS, MCSD, MCSE+I, MCDBA
    Website http://www.WhartonComputer.com
    Blog http://MyProjectExpert.com contains my field notes and SQL queries

    Thursday, January 17, 2013 3:59 AM
    Moderator
  • Hi Callistus,

    Executable "ProjectServerOlapCubeGenerator.exe" is used by Project Server while OLAP cube from PWA.

    When we click on save and build now, this particular executable is initiated with specific parameter which tries to locate database server where project published database is hosted. If PWA site is provisioned using SQL server :portname and OLAP is being built using SQL Analysis Server with Alias, you may notice following error message

    Failed to build the OLAP cubes. Error:

    Are you using SSAS and SQL server on same server or different?

    Did you configure SQL alias on App servers?


    Hrishi Deshpande – Senior Consultant DeltaBahn
    Blog | < | LinkedIn

    Please click Mark As Answer; if a post solves your problem or Vote As Helpful if a post has been useful to you.This can be beneficial to other community members reading the thread.

    Thursday, January 17, 2013 6:39 AM
    Moderator
  • Hi Hrishi,

    Thanks for taking the time to respond. I have SSAS and SSDS on the same machine, and SQL alias is configured on the App server. I am able to see all the (SharePoint and Project Server) databases on the server! Yet building the OLAP cube fails except when we go to the default port.

    I am Stumped!!


    VergeOn - Callistus Lucien

    Thursday, January 17, 2013 7:50 AM
  • Thanks Michael, for taking the time to respond. I will look into the web.config file tomorrow. Everything seems to work except for SSAS and the building of the OLAP cube!

    VergeOn - Callistus Lucien

    Thursday, January 17, 2013 7:56 AM
  • If you created an alias for SQL it must be 32bit alias, since cube builder ProjectServerOlapCubeGenerator.exe is a 32bit application. You have to do this if you have a named instance instead of the default name. Also make sure the firewall is not blocking the port number(s) for SQL.
    This article is talking about Project Server 2007 but has information that may help your case
    http://blogs.msdn.com/b/brismith/archive/2009/10/08/project-server-2007-another-olap-cube-building-gotcha-is-your-alias-32-or-64-bit.aspx

    Ali Al . Consultant & IT Manager.
    epma
    FB TW IN

    Thursday, January 17, 2013 2:16 PM
  • Hiya,

    Just a side question, you have SQL Browser service running, right?

    Thursday, January 17, 2013 3:54 PM
  • Policy is to have it turned off, but we started it to test, and there is still an error connecting to the SSAS.

    VergeOn - Callistus Lucien

    Thursday, January 17, 2013 11:16 PM
  • Callistus,

    What is the exact error message?

    Enable TCP\IP and named Pipe protocol using cliconfg on Project App Server?

    1. Create one text file on APP server, change file extension from .txt to .uld
    2. Double Click on this file and click on provider tab and select OLEDB provider for SQL server, click next
    3. Enter your SSAS alias and select windows integrated security, click on TEST Connection.
    4. Connection should be successful. If failed try with server name:port number , validate the result.

    Another option is to try processing the cube from SSAS

    1. Save cube build setting from PWA with SSAS DSN name.
    2. Connect to SSAS using SQL Server management studio
    3. Expand databases and right click on previously built OLAP cube and click on process and ok.

     


    Hrishi Deshpande – Senior Consultant DeltaBahn
    Blog | < | LinkedIn

    Please click Mark As Answer; if a post solves your problem or Vote As Helpful if a post has been useful to you.This can be beneficial to other community members reading the thread.


    Thursday, January 17, 2013 11:24 PM
    Moderator
  • Hiya,

    Just to be sure, the server is listening on the assigned port?
    netstat -an |find /i "listening"

    Secondly you would want the SQL Browser service to be running.

    SQL Server Browser Service
    http://msdn.microsoft.com/en-us/library/ms181087%28v=sql.100%29.aspx
    Friday, January 18, 2013 7:03 AM
  • Yes, the server is listening on the assigned port.

    Business policy is to not have the SQL Server Browser Service running, but we did enable it for testing and still unable to connect to SSAS.


    VergeOn - Callistus Lucien

    Wednesday, January 23, 2013 6:19 PM
  • Unfortunately I don't have access to SSAS nor the management studio console and getting help is like pulling teeth!

    VergeOn - Callistus Lucien

    Wednesday, January 23, 2013 6:21 PM
  • No Alias created and named instance. Using the default instance.

    VergeOn - Callistus Lucien

    Wednesday, January 23, 2013 6:22 PM
  • Hi

    try to configure the SQL alias with the port as weel on the database server as on the app server.

    Project Server forwards the connection string for the reporting database to the Analysis Server. This will use the alias configured on the Project Server machine and therefore the Analysis Server needs to know this alias.

    Regards

    Christoph


    Christoph Muelder | Senior Consultant, MCSE, MCT | SOLVIN information management GmbH, Germany

    Friday, January 25, 2013 1:03 PM
  • Finally! So if anyone is interested...

    First thank you one and all for your responses and help. I confirmed a number of your configuration suggestions which led me to this solution...

    • The tech guys have configured the network to use the FQDN to reference the SQL Server.
    • A number of processes and instances seems to want to use the server name.
    • I had created an Alias using the FQDN to be consistent with the tech guys' request.
    • I had success renaming the Alias to the server name (or a shorter name?!) pointing to the FQDN.
    • Reconfigured SharePoint and re-provisioned PWA to use the new Alias

    Eureka! The cube was successfully built. I am hoping this resolves all other related issues.

    Again thank you all,
    Callistus


    VergeOn - Callistus Lucien


    Tuesday, February 19, 2013 8:56 PM