Unable to configure System DSN and create Data connection

Question

Hi Experts,

I received below information from a user to create a Gallery Data connection -

I edited some alphabets due to security concerns.

DSX

Server: LAUSP-WSQL0289.dceinc.com
Port: 1433
Databases: BBM2
ID: DCEINC\SVD_GFCJ_PROD
Password: Si.A]Z(Z9t7VG2Rnma=pa[5-

We installed 'ODBC Driver 17 for SQL Server' in the Server and also confirmed that the user is using the same driver in his laptop.

Then we tried to configure ODBC System DSN from Server and received error (Attached screenshot)

--------------------------------------------------------------------------------------------------------------

Since above steps were not working, so tried to test DSN less connection but again received error (Attached screenshot)

odbc:DRIVER={ODBC Driver 17 for SQL Server};UserID=DCEINC\SVD_GFCJ_PROD;PWD={Si.A]Z(Z9t7VG2Rnma=pa[5-};DATABASE=BBM2;SERVER=LAUSP-WSQL0289.dceinc.com;Integrated Security=SSPI;

Need help to fix the issue. What will be the correct syntax if we want to create DSN less connection?

ODBC error.JPG

DSN less 1.JPG

fmvizcaino · Answer

Hi @EN6924 ,

These are the ones I would try. The Integrated Security=SSPI part is when you are using windows authentication, so you can remove it from the string.

Driver={ODBC Driver 17 for SQL Server};Server=LAUSP-WSQL0289.dceinc.com;Database=BBM2;UID=DCEINC\SVD_GFCJ_PROD;PWD='Si.A]Z(Z9t7VG2Rnma=pa[5-';

Driver={ODBC Driver 17 for SQL Server};Server=LAUSP-WSQL0289.dceinc.com;Database=BBM2;UID=DCEINC\SVD_GFCJ_PROD;PWD="Si.A]Z(Z9t7VG2Rnma=pa[5-";

Best,

Fernando Vizcaino

EN6924 · Answer

Hi,

The password is: Si.A]Z(Z9t7VG2Rnma=pa[5-

I tried using -

odbc:DRIVER={ODBC Driver 17 for SQL Server};UserID=DCEINC\SVD_GFCJ_PROD;PWD={Si.A"]"Z(Z9t7VG2Rnma=pa"["5-};DATABASE=BBM2;SERVER=LAUSP-WSQL0289.dceinc.com;Integrated Security=SSPI;

Still it's same error showing.

fmvizcaino · Answer

Hi @EN6924 ,

It looks like it`s a password issue. Since you have special characters, I would try wrapping between single or double quotes.

But from my research, maybe you will have to remove some characters from the password.

https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Ideas/Stop-passwords-from-escaping-characters-into-a-OLEDB-ODBC/idi-p/21554

Best,

FErnando Vizcaino