• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Working with SQL databases with Excel 365

Recently we upgraded our servers and moved from Excel 2013 to Excel 365. I am enjoying power query but am frustrated in that I can't find a way to have an excel workbook save the Username and Password required to connect to the SQL database. Also after I manually input the credentials I get nagged that excel cannot connect with an encrypted connection . . . . then I have to accept that I will connect with an unencrypted connection.

The Username and Password credentials have been setup to restrict data access on the server side. This is while I feel safe in storing the credentials with the spreadsheet.

Are there any suggestions as to how I can avoid having to log into each database I connect to manually?
 

Chihiro

Excel Ninja
It is automatically stored (though locally).

You should see them in Get Data -> Data Source Settings. Under Global permissions. You can adjust setting as needed.
59234

You can also access data source directly from "Recent Sources".
 
The "storing" you refer to seems to be in memory of the computer. If you reboot you are back at ground zero putting in the User Id and Password again. This becomes a bit tedious when you are using several data sources. It seems Microsoft is intentionally preventing saving credentials. If you set up an ODBC you can include a non-credential connection string. Why else can't you put the credentials in the connection string . . . other than "Security".

Hopefully I'm missing some obvious way to do this but I've also now seen a post by Tisky Sheng a moderator on the Microsoft Community forum saying that this can't be done.
 

Chihiro

Excel Ninja
Unless you are talking about something completely different. Credentials are stored, even after restart.

I connect to multiple dbs/SQL Servers. Have not had to supply credentials in a while. I restart my terminal on weekly basis.

If you can't get it to work, you can also try creating *.ODC files.
 

Chihiro

Excel Ninja
FYI - Connection properties are stored in User.Zip file and is encrypted.

Usually located in C:\Users\Username\AppData\Local\Microsoft\Office\16.0\PowerQuery

Also, what is mentioned in the post you linked, is transferring Credential data with Workbook to another terminal/user. Meaning credential embedded in the workbook/powerquery. Which is another issue. For that, you'd need ODC file exported and used.
 
Interesting. My intention is to distribute the file within our organization so may have to look at ODC files.

Your comment about the path is interesting as I can find no directory with the word PowerQuery. This is likely because we are running on Citrix. It may be we need a tweek of how office is set up. Excel may be trying to write to a similar location and can not find it.
 

Chihiro

Excel Ninja
Ah, yes Citrix. That would be the likely culprit. Brings back memories on back and forth I had with client's IT department.
 

asamm

New Member
Interesting. My intention is to distribute the file within our organization so may have to look at ODC files.

Your comment about the path is interesting as I can find no directory with the word PowerQuery. This is likely because we are running on Citrix. It may be we need a tweek of how office is set up. Excel may be trying to write to a similar location and can not find it.

Hi,
Did you manage to get this working in a Citrix environment?
We're trying to migrate a user's global permissions from one profile location to another by copying the User.zip file, however the global permissions are not being loaded from the new windows profile.

Thanks,
Andrew
 
Top