BookmarkSubscribeRSS Feed
mal
Calcite | Level 5 mal
Calcite | Level 5

I’m trying to create a database connection to a remote instance of SQL Server in DM Studio. I can create the connection, and it works (I can see the tables and their data), but DM Studio will not save the authentication credentials for some reason. Every single step of looking at the database, or table, or whatever, it prompts me for the password. And in a Data Job, I can see this data source connection and select it for the job's data source, but when attempting to preview I get a "Data source name not found and no default driver selected" error.

I have saved the credentials in DM Studio, and DM Studio thinks that I’ve saved them, but when I go to the <DataConnectionName>.cfg file, all it has are:

#

# connection information for DSN: <DataConnectionName>

#

[Empty. No stored information.]

Whereas the normal *.cfg file would have something like:

#

# connection information for DSN: <DataConnectionName>

#

UID = !! 'xxuxjxvjidivnidvn=='

PWD = !! 'ghrotknpotUfQ=='

I tried generating the encrypted UID and PWD and copying the encrypted content into the empty file, but that doesn’t work. That makes me think that maybe a SQL Server connection needs additional information in the DataConnectionName.cfg file.

I tried both the “DataFlux 32-bit SQL Server Wire Protocol”, and the “SQL Server” drivers with the same result.

Why is DM Studio allowing us to connect, but won't save the credentials or finalize the saved connection? This is DM Studio 2.6, BTW.

Thanks!

- mark

7 REPLIES 7
skillman
SAS Employee

Does the id that installed DM Studio have write access to the configuration file location?

mal
Calcite | Level 5 mal
Calcite | Level 5

That's a great question, and one of the first things I checked. Yes, it does. The DM Studio is writing these stored credential files to C:\Users\<username>\AppData\Roaming\DataFlux\dac\savedconn. It created a stored credential file there (a <DataConnectionName>.cfg file), but it is empty of credentials.

Thanks!

  - mark

skillman
SAS Employee

You mentioned DM Studio will not save the connection, but I did not see where you right-clicked on Data Source Name and chose 'Save User Credentials' in DM Studio. When they are saved the column labeled 'User Credentials Saved' will change to yes (which one of mine does in the screenshot):

2015-02-11 10_06_24-DataFlux Data Management Studio 2.3.png

mal
Calcite | Level 5 mal
Calcite | Level 5

Hi skillman,

I did save them, DM Studio then creates the shell <DataConnectionName>.cfg file, but there are no encrypted credentials inside the file. See below screenshot:

Thanks for your help!

  - mark

skillman
SAS Employee

Mark,

In the ODBC connection setup, what Authentication Method did you select? Mine is set to Kerberos, which will pass the credentials from the PC to the database.

DataFlux Data Management Studio 2.3_2015-02-11_21-06-40.png

I don't have to save the credentials in DM Studio, and when I click Test Connect I get the 'Connection Established' dialog box.

-shawn

mal
Calcite | Level 5 mal
Calcite | Level 5

Hi skillman,

Because we're using the 'SQL Server Authentication' method of logging into the SQL Server DB, we can't use Kerberos.

We found out what the problem was. The password that was given to us was randomly generated, and it contained a ')' character. This password works in SQL Server, but there is a defect with the DF encryption tool that can't handle the ')' character. Apparently the encryption portion of saving credentials would just silently fail. SAS knows of this defect.

skillman, thank you very much for all of your time. I do appreciate it. I know that everyone is busy.

  - mark

Thanks for continuing to help us!

skillman
SAS Employee

Those silent errors are never fun when debugging.

Glad you are up and running!

-shawn

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 7 replies
  • 1820 views
  • 1 like
  • 2 in conversation