Microsoft Excel for Mac not storing SQL Server connection password

Question

Hello,

I have a file setup in Microsoft Excel for Mac, Version 16.9 with a SQL Server connection that works perfectly for all users. But it asks them for the password every time they open it.

The same file on a Windows PC, using regular Excel, can store the password with a checkbox option to "Save password" in the Connection String.

Is this same feature available in the Excel for Mac software? Or are there plans to make it available in future updates?

Thanks,

K

Answer 1

Hi KatarinaZG,

We would like to know more about the situation. Here is a similar thread for your situation. Could you please provide the below information to help confirm it?

1. Does the issue occur to all the SQL server connections? Is it the same situation in the thread above?
2. A screenshot to show the symptom.
3. Detailed steps and related screenshots about how you encoutner the issue.
4. Your Mac operating system version.

Thanks,

Tina

Answer 2

I really should add this to my tutorial pages.

The password goes into the connection string in Data > Connections > Properties. I'm not at work where I have lots of examples. I'll try to remember to give you an example on Monday.

Answer 3

Ok, Here's the example screen shot (generic information). Syntax is important. You have to use a comma and the port number. Unfortunately, the password is in plain sight for anyone who knows where to find it. Fortunately, hardly anyone knows where to look, and its buried pretty deep.

Excellent behavior: When you edit the query in the connection string field shown below, the workbook requires no changes to work on either a Mac or a Windows PC.

Answer 4

@ Tina - It is better to not answer a question you don't know the answer to rather than pester the question-originator for useless information as you waste time trying to find out the answer.

BTW, who is "we?" I certainly don't need any of the information you asked for to answer this question. Please, speak for yourself. Katarina provided everything you need to answer this question.

Answer 5

Ok, Here's the example screen shot (generic information). Syntax is important. You have to use a comma and the port number. Unfortunately, the password is in plain sight for anyone who knows where to find it. Fortunately, hardly anyone knows where to look, and its buried pretty deep.

Excellent behavior: When you edit the query in the connection string field shown below, the workbook requires no changes to work on either a Mac or a Windows PC.

Thank you so much for the information and the screenshot. That's exactly how I thought it should be set up because it works perfectly on my Windows PC.

However when I set it up that way on the Mac, the password disappears out of the connection string when the Connection Properties window is closed. If I make any other changes to the connection string or command text, everything is saved. But that PWD=password bit is always gone the next time it's opened. Therefore it's not saved.

The only difference between my file and your example is that I'm referencing the database by IP address.

I set the connection string up as:

DRIVER=SQL Server;SERVER=192.168.1.200,12345;UID=UserID;PWD=password;APP=Microsoft Office 2016;WSID=MachineID

And as soon as I click OK, and then go right back into properties, the connection string is missing the PWD bit:

DRIVER=SQL Server;SERVER=192.168.1.200,12345;UID=UserID;;APP=Microsoft Office 2016;WSID=MachineID

I thought maybe my file was corrupt so I created a brand new file and just set up a simple connection. Same issue. Any thoughts?