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.
Good Afternoon. Is this thread also related to case 01096878: CASM 17.1 with MSSQL named pipe disabled? Thanks and kind regards, Louis.
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.
I think it's a BUG. Big Bug. Here is my test result.
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:
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
restart sdm service.
3. Login SDM successful.
TCP/IP: Enabled. IPALL TCP Port = 1561
Named PIPE: Enabled
Named PIPE: Disabled
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.
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
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.
I would suggest installing the SQL Client Tools (Full) and the SQL Management Components at a minimum.
Those are already installed, I believe Chison wants to ask about how to set up the local system DNS.
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
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 hostcom.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.".
try to not use "localhost" as alias when creating your DSN but something different and use that in your sdm config.
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.
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
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.
so do this work in SDM too now?
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.
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.
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:
Hope this will fixed in the next patch or at least documented.