0

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:

img1

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:

img2

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?

  • 3
    I don't know of a situation where I'd want a device not attached to our domain to access my SQL servers. That's why we use VPNs for machines that are not permanently attached to the domain. Someone may have a better answer for you but what happens if someone's personal laptop gets stolen and they have that excel spreadsheet on their desktop? You've now opened up a potentially large security hole and whomever has that laptop now has 100% direct access to your SQL instance, even worse if it's a user with some elevated privs. VPNs usually require 2 factor authentication for this very reason. – Kris Gruttemeyer Mar 26 '15 at 18:13
  • I second Kris on this one. Once you un-check 'Use Trusted Connection' you're not going to be able to use domain creds to connect. Either have the users run a script that requires them to launch via logging into the domain, or settle for a SQL Login. VPN is the way to get around all these issues most elegantly though. – LowlyDBA Mar 26 '15 at 18:44
  • We want to use Windows auth, without saving their domain credentials, on remote/laptop users for exactly that security concern. Joining the laptops to the domain is currently not an option. VPN tunnels are used. The question remains: is it possible to get Excel to use Windows/Domain Authentication from an un-trusted (ie. non-domain) system? If so, how? Or, is Excel limited to only using inherent application rights for Windows Auth? – ltwally Mar 26 '15 at 19:40
  • If you don't want to store the credentials (Windows Credential Manager can do this) then no. However, if you are willing to store them in WCM then the answer would be yes. – Mister Magoo Mar 26 '15 at 23:21
  • @MisterMagoo I have not been successful with that method, either. Can you confirm that you have successfully used WCM to do this? – ltwally Mar 26 '15 at 23:55
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.