We have an Excel spreadsheet that uses a Data Connection to a SQL Server database. The database server is joined to our domain, and Windows Authentication is enabled.
Here is a sample of the connection string (with revealing info obfuscated):
Description=DataConnection;DRIVER=SQLServer;
SERVER=10.x.y.z;UID=domain_user;Trusted_Connection=Yes;
APP=Microsoft Office 2010;DATABASE=DatabaseName
And the Authentication Settings are set to "Windows Authentication"
This works perfectly fine from a domain-joined / trusted machine. It doesn't even ask for credentials; it just works flawlessly when I hit Refresh.
It will work from a non-domain / un-trusted workstation, if I use SQL Authentication. But we are trying to move away from that.
Likewise, it will from a non-domain machine using Windows Authentication if I use "runas" to launch Excel with domain credentials, as follows:
runas /netonly /user:domain\user %path-to-excel%\excel.exe
However, using "runas" is a kludge, and our employees will almost certainly have issues… even if I were to create batch files to help automate it.
When launching Excel normally (without using "runas") from a non-domain machine, it gives this error when I hit Refresh:
Upon hitting OK, I have the option to un-check "Use Trusted Connection" and manually enter a user/password. I put in a valid user that would be able to connect fine from a domain-joined machine, or that worked fine using "runas". This is the error it gives with that:
At this point, I have not a clue what to do to get Excel to successfully connect with domain credentials from a non-domain machine.
Ideas?
Best Answer
Our solution ended up being ShellRunas.
https://technet.microsoft.com/en-us/sysinternals/cc300361.aspx
By creating custom shortcuts to launch Excel using the /netonly switch, we can have them enter their domain credentials and then open the needed spreadsheets. It's not 100% transparent, but it's good enough.