topic Re: Can't save data connection in Data Management Studio in SAS Data Management

Can't save data connection in Data Management Studio

mal — Tue, 10 Feb 2015 23:39:39 GMT

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

Re: Can't save data connection in Data Management Studio

skillman — Wed, 11 Feb 2015 13:32:13 GMT

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

Re: Can't save data connection in Data Management Studio

mal — Wed, 11 Feb 2015 14:55:53 GMT

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

Re: Can't save data connection in Data Management Studio

skillman — Wed, 11 Feb 2015 15:07:55 GMT

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):

Re: Can't save data connection in Data Management Studio

mal — Wed, 11 Feb 2015 15:17:25 GMT

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

Re: Can't save data connection in Data Management Studio

skillman — Thu, 12 Feb 2015 02:15:36 GMT

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.

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

Re: Can't save data connection in Data Management Studio

mal — Thu, 12 Feb 2015 15:11:24 GMT

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!

Re: Can't save data connection in Data Management Studio

skillman — Thu, 12 Feb 2015 15:54:37 GMT

Those silent errors are never fun when debugging.

Glad you are up and running!

-shawn