Excel connect to SQL Server using a different windows user name

Question

hello,

I am trying to connect to SQL Server in MS Excel using a different windows user credential other than current logged on windows user ID.

using SQL Server database user name is not an option for me.

Is there any function that can impersonate in Excel VBA? or a connection string that we can add windows user ID and password to pull data to Excel sheet?

Answer 1

Your only 2 options in SQL is Windows auth (which always uses the current Windows user login) or SQL auth and they are not interchangeable. The correct solution here is to either use the actual WIndows user account or switch to SQL auth.

The only workaround would be to impersonate the user within the code. You can refer to this old thread on how to do that. But there are some caveats that you strongly need to consider first.

• To impersonate the user you need the username and password. Since this is being done inside an Excel file script you would either need to prompt the user for this information or hard code it. If you hard code the information then you're giving anyone access to this file credentials to the account effectively. The only reasonable security solution here is to prompt the user but now everybody using the file needs that information so it is not much more secure.
• VBA in Office is becoming more and more deprecated in lieu of the newer Javascript programming model and JS won't let you do impersonation for obvious reasons. So your solution is going to be dependent on stuff that is deprecated.

I would reevaluate why you need to use different credentials and resolve that issue instead. For example if the SQL in question needs to be accessed by many different users then set up some AD groups to contain the allowed users and then add the AD group (or even just the users) to SQL with the necessary rights. This is the most secure approach and allows you to "revoke" permissions for a user if needed.

If the issue is one of licensing (you only have 1 user CAL for example) then this is actually circumventing the licensing rules so you should just pay for more licenses. Alternatively create an API.

If the issue is that this is a legacy DB and you need access to it then consider creating an API that interacts with the database and then have Excel call the API instead. This eliminates the need for credentials and allows you to use the appropriate credentials in the API to talk to the database. Of course this mandates knowing how to write an API and then hosting it somewhere so it can be called.

Answer 2

Thank you, Michael Taylor,
It works greatly.
At the beginning, I completely misunderstood how Excel with VBA and SQL Server connection works.

It makes sense now; it is much simpler and secure as well to use Windows logged-in user credentials.

No wonder so many people are following you.

Answer 3

SQL Server doesn't allow/support impersonation of different Windows accounts for login.

But you can use th command "runas" to start MS Excel with a different Windows account, see

https://learn.microsoft.com/en-us/previous-versions/windows/it-pro/windows-server-2012-r2-and-2012/cc771525(v=ws.11)