0

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?

Improve this question
2
  • SQL Server Management Studio v17.9.1 isn't a version of SQL Server, it's a version of SSMS; an IDE used to interact with SQL Server. As for the problem, if the connection "works" when you remove Trusted_Connection=YES then this sounds like you're saying your passing that parameter along with some windows credentials. That isn't how it works. You either pass the Login and Password, and use SQL Authentication, or use a Trusted Connection. But this is a guess, based on the information we have. If you review the SQL Server's logs, you'll see the real authentication failure reason. Commented Jul 21, 2020 at 11:47
  • @Larnu Thanks a lot, i just updated the SQL Version. But did I understand you correct: If the connection string includes a SQL User name and password, there is no need for a Trusted_Connection (because it doesn't connect over Windows Login). By the way, which SQL Logs do you mean? Commented Jul 21, 2020 at 11:57

2 Answers 2

Reset to default
1

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

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks a lot, that was it. Trusted_Connection ignores the credentials used inside the connection string. Now it makes sense.
0

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.