Benoit...........
None of the Service Desk Manager versions (14.x or 17.x) are certified/supported with SQL Server 2017.
Are you able to reproduce the behavior with a supported SQL Server version (SQL 2014 for SDM 14.x and SQL 2016 for 17.x)?
------------------------------
Paul Coccimiglio
Principal Support Engineer
Broadcom Inc.
------------------------------
Original Message:
Sent: 08-29-2019 03:37 PM
From: Benoit Laviolette
Subject: Configuring SDM to connect to new SQL Server DB
Hi everyone,
I think it might be a great idea to share that here as my question might help others who would (or will have) that problem in the future.
We are in the process of migrating (finally ! our database from SQL Server 2008R2 to SQL Server 2017) and for security purposes our DBAs don't use "instance named" database anymore as a way to connect to the database. The port has to be specified.
I tried moving my actual SDM (14.1.05.1) to the newly copied mdb on the new server using pdm_configure wizard. When entering all credentials for the new SQL Server (server node, database name, sa (and password), mdbadmin (and password), and port). I get an error message impossible to connect to SQL Database.
Configure.log shows this (Installation of CA SDM is in French):
08/28 15:27:41.612 INFO SQLDatabasePage.java 520 ## Background Servername () ; Primary Servername () ; Local Servername (MMTRLPALDAPP137)
08/28 15:27:45.543 INFO SQLDatabasePage.java 763 Running: sqlcmd -S testServer.local -U sa -P <password> -l 5 -i tmp_sql.sql
08/28 15:27:51.211 INFO SQLDatabasePage.java 783 Sqlcmdÿ: erreurÿ: Microsoft ODBC Driver 13 for SQL Serverÿ: Fournisseur de canaux nomm‚s : Impossible d'ouvrir une connexion … SQL Server [2]. .
08/28 15:27:51.211 INFO SQLDatabasePage.java 783 Sqlcmdÿ: erreurÿ: Microsoft ODBC Driver 13 for SQL Serverÿ: D‚lai d'attente de connexion expir‚.
08/28 15:27:51.211 INFO SQLDatabasePage.java 783 Sqlcmdÿ: erreurÿ: Microsoft ODBC Driver 13 for SQL Serverÿ: Une erreur li‚e au r‚seau ou sp‚cifique … l'instance s'est produite lors de l'‚tablissement d'une connexion … SQL Server. Le serveur est introuvable ou n'est pas accessible. V‚rifiez si le nom de l'instance est correct et si SQL Server est configur‚ pour autoriser les connexions distantes. Pour plus d'informations, consultez la documentation en ligne de SQL Server..
08/28 15:27:51.211 ERROR SQLDatabasePage.java 791 Failed to check sql user
It meant "timeout expired" and connection could not be done. If I try to "upgrade" to CA SDM 17.2 using the setup.exe on the DVD I can go through first screen asking for databases credentials (except retrieving "community administrator" for Unified self service) but when it is time to install all the components it fails when it's time to update content in the mdb database. And again configure.log shows the same errors.
What I found out is that even when defining port number in the pdm_configure wizard or in the installation wizard, all further "sqlcmd" done by SQLDatabasePage.java never specify the port number in the "sqlcmd" string.
For example (working outside of SDM in a command prompt)
Not working
P:\>sqlcmd -U mdbadmin -P password -S testServer.local -d mdb -Q "Select * from call_req" > C:\z_sqlcmd\test_2_2017.dat
Working (Expecting 55555 a port number for example)
P:\>sqlcmd -U mdbadmin -P password -S testServer.local,55555 -d mdb -Q "Select * from call_req" > C:\z_sqlcmd\test_2_2017.dat
But for now I can't figure out how to have SQLDatabasePage.java write the specific port. I tried adding ,55555 in the database node in pdm_configure but validation fails, I tried changing database node and port number in Nx.env but that didn't change anything.
Anyone could help for that issue?
Thanks