Excel Data Connection to SQL Server for Domain Accounts using Windows Authentication

Question

we have a requirement to allow users to use their Domain Ids for Windows Authentication while connecting to our database Via Excel.
current implementation works with Sql Server Authentication. but for security risks, we need to update to Windows Authentication.

I have been reading around & i see suggestions to create Active Directory(AD) groups & assign users to this group. In management studio , we could assign them required permissions. However, i'm not convinced on this & need help understanding how a user will change the Data Connections in excel to be able to Use this new AD group & connect to database.

Links I have gone through :
https://itectec.com/database/sql-server-ad-group-cannot-connect-to-sql-from-excel/
https://stackoverflow.com/questions/5029014/how-to-add-active-directory-user-group-as-login-in-sql-server
https://www.mssqltips.com/sqlservertip/6702/sql-server-windows-authentication-with-users-and-groups/

Can someone help me understand the working of this? I dont have much exp on admin or on Active Directory.
Also is using AD groups the only solution to this? There are multiple users from a Domain who use this excel connection with SQL Auth currently.

Answer 1

how a user will change the Data Connections in excel to be able to Use this new AD group & connect to database

In Excel go to tab "Data" => "Connections" => "Properties" => "Definition".
Remove "UserID" and "Password" (UID/PWD) and add

Trusted_Connection=yes;

to logon using Windows Authentication.

See https://www.connectionstrings.com/sql-server/

Answer 2

The gray area for me is, if I assign this user to an Active Directory group (TestADGroup) & give this group permissions on database server, then how will excel use this AD group to allow userA to connect to Database.

Excel will just pass the bucket, or more precisely the login tokens from Windows. The user was authenticated by Windows, and SQL Server gets this information.

Are you already using Windows authentication when connecting from SSMS? This is exactly the same thing!

Answer 3

Hi @Vijayvargia, Sudipta ,

Of course Windows authentication, managing access from at the domain level can simplify account administration.
Connecting Through Windows Authentication, this acticle may be help you.
Trusted_connection means the connection is using the current logged in user details and pass these on to SQL Server to authenticate with (Windows Authentication).

Connect with Microsoft Excel to Microsoft SQL only works with trusted connection

-------------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.