CODEWITHSUNDEEP
sql-serverexceldatabase
Baku Bakar
Baku Bakar

Reputation: 462

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

Issue:

I would like to connect from Microsoft Excel 365 to a database inside the Microsoft SQL Server 2016.

The connection string includes already an username and password from an account, which is created on the specific SQL Database as user.

There is one issue, when I would like to extend the connection string with this: Trusted_Connection=YES

In that case, it doesn't work. I'm receiving the following error message:

SQLState: '28000'
SQL Server-Error: 18456
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'DOMAIN\CurrentLoggedInWindowsUsername'

When I remove the connection string Trusted_Connection=YES the connection works very well.

Workaround:

I did find one workaround. When I create on the MS Sql Server an login account for the specific windows user account, the user is able to connect to the database with a trusted connection.

But I don't like to do this, because I already created a specific user, which is mentoined in the connection string. In additional, I would like to share this excel with multiple users, which I don't want to create for everyone an login, inside the Microsoft SQL Server.

Please note, that I already make sure that the SQL Server and windows Authentication mode is set.

Question:

Do you have an idea, how I can make sure to make a trusted connection string, without adding each windows user account inside the Microsoft SQL?

Upvotes: 0

Views: 4699

Answers (2)

user7415753
user7415753

Reputation: 198

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).

If you want to use SQL authentication (username and password) then don't use this flag.

Have a look through this article, might be helpful explaining the different authentication methods. https://learn.microsoft.com/en-us/sql/relational-databases/security/choose-an-authentication-mode?redirectedfrom=MSDN&view=sql-server-ver15

If you want to use windows authentication (trusted_connection flag) and don't want to individually add the users, the alternative you can have is to add users to a domain group and set that group with the correct permissions. But from reading between the lines of your question you should just ignore this flag if you want to use SQL authentication

Upvotes: 1

Shilpa D J
Shilpa D J

Reputation: 46

Create a functional user account in SQL Server and add the user account in the Excel VBA code. Make sure that this account has RW access to all the required DB objects. Once it is added in the excel all the users will be able to access the DB without any issues.

Upvotes: 0

Related Questions