This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(83.2, 62%, 18%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EVS%3C/text%3E%3C/svg%3E)
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.
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data
Answer accepted by question author
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.
Thanks for your answwer.
With this, it seems that the Excel data connection will use the User account to connect to DB.
Example : a user within a domain XYZ. : XYZ/userA.
Excel will use Windows Authentication : XYZ/userA.
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.
I hope I'm clear about my doubts.
Please help.
how will excel use this AD group
Excel don't, but Windows/Domain will handle the authentification & group assignment. Believe me, it works with AD groups.
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!
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(163.2, 84%, 25%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EYM%3C/text%3E%3C/svg%3E)
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.