Clarity Service Management

Expand all | Collapse all

CASM 17.1 with MSSQL named pipe disabled and not using default port

Jump to Best Answer
  • 1.  CASM 17.1 with MSSQL named pipe disabled and not using default port

    Posted 05-25-2018 03:14 AM

    Hi all,

     

    We are implementing CA SDM and SC 17.1, during the installation it had error during common table configuration.

    It was caused by disabled MSSQL named pipe, the installation was completed normally with the named pipe enable. However, the client security baseline policy required the named pipe to be disabled.

    Does disabling the MSSQL named pipe affect any functions of CA SDM, SC, EEM, and ITPAM?

    The OS is Windows 2012 R2 and MSSQL is ver.2016.

     

    Best regards,

    Conan



  • 2.  Re: CASM 17.1 with MSSQL named pipe disabled

    Posted 05-25-2018 06:53 AM

    Good Afternoon.

    Is this thread also related to case 01096878: CASM 17.1 with MSSQL named pipe disabled?

    Thanks and kind regards, Louis.



  • 3.  Re: CASM 17.1 with MSSQL named pipe disabled

    Posted 05-25-2018 06:56 AM

    And a Good Afternoon again.

    As seen in an earlier reported case, the answer/resolution then reads:
    "Yes, SQL server named pipes have to be enabled."

    Please advise. Kind regards, Louis.



  • 4.  Re: CASM 17.1 with MSSQL named pipe disabled

    Posted 05-25-2018 11:34 PM

    Hi Louis,

     

    I think it's a BUG. Big BugHere is my test result.

     

    Environment:

       CA SM 17.1

       SDM and DB are different server.

     

    If DB TCP listening port is the default port 1433, then it is ok to just enable TCP/IP and disable named pipe. SDM works normally.

     

    If DB TCP listening port is not default port (e.g 1561 per customer policy), then I have to enable both TCP/IP and named pipe, to make SDM work.

     

    From my obversation, SDM is using below command to connect to the database.

       sqlcmd -S demodb -U *** -P ***

     

    I think SDM incorrectly ignored the DB port config (neither the port I inputted in pdm_configure screen or in the @NX_DB_PORT of nx.env). 

     

    To repeat this issue:

    Test#1

    1. In DB server, in Sql Server Configuration Manager, modify the protocols of mssqlserver.

          TCP/IP: Enabled.  IPALL TCP Port = 1433

          Named PIPE: Disabled 

       restart sql server service.

    2. In SDM server, modify nx.env

          @NX_DB_PORT=1433

       restart sdm service.

    3. Login SDM successful.

     

    Test#2

    1. In DB server, in Sql Server Configuration Manager, modify the protocols of mssqlserver.

          TCP/IP: Enabled.  IPALL TCP Port = 1561

          Named PIPE: Enabled

       restart sql server service.

    2. In SDM server, modify nx.env

          @NX_DB_PORT=1561

       restart sdm service.

    3. Login SDM successful.

     

    Test#3

    1. In DB server, in Sql Server Configuration Manager, modify the protocols of mssqlserver.

          TCP/IP: Enabled.  IPALL TCP Port = 1561

          Named PIPE: Disabled

       restart sql server service.

    2. In SDM server, modify nx.env

          @NX_DB_PORT=1561

       restart sdm service.

    3. Cannot start SDM. Cannot access SDM login page. sql connection error found in stdlog.

     

    Please help to contact technical team to confirm this bug and provide fix.

     

    Thanks.

    Regards,

    Chison Cai

    chison.cai@igsl-group.com



  • 5.  Re: CASM 17.1 with MSSQL named pipe disabled

    Posted 05-28-2018 07:38 AM

    To quickly overcomes this until support come back, you may just want to create a local system DSN on port 1433 that point to your DB server on port 1561 and rerun the pdm_configure to use that newly created DSN. You will of course need to install the SQL remote components locally first.

    We used that trick in older version of SDM in the past to run our MDB in SQL instances.

    My 2 cents

    /J



  • 6.  Re: CASM 17.1 with MSSQL named pipe disabled

    Posted 05-28-2018 09:58 AM

    Hi JEROME, thanks for reply.

    Could you share the detail of "SQL remote components"? I googled it but the words are too generic to locate the correct component.

     

    Thanks.



  • 7.  Re: CASM 17.1 with MSSQL named pipe disabled

    Posted 05-28-2018 10:40 AM

    Chison.......

     

    I would suggest installing the SQL Client Tools (Full) and the SQL Management Components at a minimum.



  • 8.  Re: CASM 17.1 with MSSQL named pipe disabled

    Posted 05-28-2018 10:26 PM

    Those are already installed, I believe Chison wants to ask about how to set up the local system DNS.



  • 9.  Re: CASM 17.1 with MSSQL named pipe disabled

    Posted 05-29-2018 01:59 AM

    Hello Conan,

    If the SQL remote components is installed you are good to go.

     

    Just open the standard ODBC data source, navigate to the System DSN tab and click on Add to create your new source.

     

    then in the next window select SQL Server as the driver

    Select SQL authentication and enter your servicedesk credentials

     

    Now the trick

    click on the client configuration button

    Select TCP/IP untick the dynamically determine port and add  the port of your remote server there.

    then click next

    tick the "" change default database  and put mdb in the field then click next

    Next again leave the default option and click finish.

    that it you now have a reference on port 1433 to your remote server on port 1561 using tcp/ip only.

     

    Hope this help

     

    /J



  • 10.  Re: CASM 17.1 with MSSQL named pipe disabled

    Posted 05-29-2018 03:16 AM

    Hi Jerome,

     

    Thanks for the reply, we tested the solution but fail. Are we missing something?

     

    2018/05/29 00.13.58.455 ERROR [AWT-EventQueue-0] [ConfigureSQLServer] The TCP/IP connection to the host
    com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host localhost, port 1433 has failed. Error: "Connection refused: connect. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.".

     

    Conan



  • 11.  Re: CASM 17.1 with MSSQL named pipe disabled

    Posted 05-29-2018 03:38 AM

     try to not use "localhost" as alias when creating your DSN but something different and use that in your sdm config.

    /J



  • 12.  Re: CASM 17.1 with MSSQL named pipe disabled

    Posted 05-30-2018 01:12 AM

    Hi Jerome,

     

    Just tested again and fail.

    However, I am able to connect to the DB with the alias using sqlcmd, I just don't understand what is the problem with the installer.

     

    Thanks,

    Conan



  • 13.  Re: CASM 17.1 with MSSQL named pipe disabled

    Posted 05-30-2018 02:51 AM

    Just have a relook for your specific case and I  have be too quick in my answer as the Sqlcmd will not support to connect to data source (only osql command with the -D parameter will do)

     

    So in stead of doing it trough data source you may need to create an alias as the SQL server level directly using the SQL configuration manager.

    See this article form Microsoft on how to do it.

    Sorry for the confusion that I have generated

    /J



  • 14.  Re: CASM 17.1 with MSSQL named pipe disabled

    Posted 05-30-2018 03:22 AM

    Hi Jerome,

     

    Thanks for helping. Creating alias via SCM is probably the same as creating via ODBC, since some of the alias I created shows up there. I think it failed because of the port setting, it tried to connect the db with <alias>,<port number>.

    While using just <alias name> is enough.



  • 15.  Re: CASM 17.1 with MSSQL named pipe disabled

    Posted 05-30-2018 03:27 AM

    so do this work in SDM too now?

    /J



  • 16.  Re: CASM 17.1 with MSSQL named pipe disabled

    Posted 05-30-2018 03:42 AM

    No, I cannot complete the DB config screen. But I want to test with alias with installed SDM

    To complete installation, I have to enable "named pipe" setting in SCM and config DB with the <db_hostname> and <non-default port number> (1561).

     

    But the installer should not just ignore the any non-default port and skip to use named pipe protocol.

     

    Summary:

     

    1. Alias cannot be use while installing SDM because port field is mandatory, which will cause error when try to connect to DB using alias and port number. (<alias>,<port number>)
    2. You can pass the db config page by using db host name and non-default port, but the installation will fail during common table installation, if named pipe protocol is disable.
    3. To install with non-default port successfully, you have to enable named pipe on SCM, which is in conflict with security policy of my client.

     

    Conan



  • 17.  Re: CASM 17.1 with MSSQL named pipe disabled

    Posted 05-28-2018 02:44 AM

    Good Morning Chison Cai.

     

    As through this thread, I can not verify/confirm whether this is a bug or not.

    The best I can propose then is for you to open a standard case.

    And then get an Engineer assigned for further analysis on this.

     

    Thanks in advance for your understanding and kind regards, Louis.



  • 18.  Re: CASM 17.1 with MSSQL named pipe disabled
    Best Answer

    Posted 05-31-2018 03:54 AM

    Hi everyone,

     

    There is not workaround for the installation, if you are not using the default 1433 port, you must enable named pipe to install SDM successfully.

    But Gordon_Preston was able to provide a workaround once the SDM was installed.

    You have to modify the NX.env file and change the following:

    @NX_DB_NODE=<dbhost>

    @NX_DB_PORT=<port> 

    to this:

    @NX_DB_NODE=<dbhost>,<port>
    @NX_DB_PORT=<port> 

     

    Hope this will fixed in the next patch or at least documented.

     

    Conan