How does Excel Data Connect to SQL Server using Windows Auth from an untrusted machine?
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:domainuser %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?
When a user connects with a specified login name and password from a non-trusted connection, SQL Server performs the authentication itself by checking to see if a SQL Server login account has been set up and if the specified password matches the one previously recorded. You need to let SQL Server know the username and password, of the people that are going to use the database connection. However, that is risky as people leave orgs and positions change and security clearances change. Create a dedicated READ-ONLY user (assuming no data changes required from Excel to SQL Server), in SQL Server, that has DBREADONLY priveleges to the database. Then change the credentials on your Excel data connection to refer to the single user created. Finally, for this to work, you'll need to let Excel know that it "trusts" the SQL Connection to an external data source, via FILE -> Options etc. This last bit has to be done on every Excel on-prem installation. If Excel is running off the cloud, it gets trickier, as you'll need to setup Connectors to your database server.