Stop SQL Server from trying to login to all databases on the instance

jluebbers · November 2, 2022 9:01AM

SQL Prompt attempts to login to all databases on an instance - even databases the current user has no access to. This pollutes the SQL Server logs with errors like this:

Date 1/11/2022 1:38:35 AM

Log SQL Server (Current - 1/11/2022 2:35:00 AM)

Source Logon

Message

Login failed for user 'DOMAIN\someuser'. Reason: Failed to open the explicitly specified database 'MyDatabase'. [CLIENT: 1.2.3.4]

Is there a way to make SQL Search not do this, or at least allow the list of databases it attempts to search be configurable?

This has been requested before in a number of places over the last few years but still is an issue in the current version.

Major KNOWN Issue --- Stop requiring product to login to all databases on server – Customer Feedback for Redgate (uservoice.com)
SQL Search causing Login failures — Redgate forums (red-gate.com)
restrict/hide database — Redgate forums (red-gate.com)

Eddie D · November 3, 2022 4:24PM

Hi jluebbers,
Thank you for your forum post.

Yes this is a known problem and one that still exists in SQL Search.

If you have not already done so, please may you vote and comment on the UserVoice forum post:
Major KNOWN Issue --- Stop requiring product to login to all databases on server – Customer Feedback for Redgate (uservoice.com)

Unfortunately there is not any option in SQL Search to prevent this from occurring or configure a list of databases for the tool to connect.

I will feedback your comments to the Development Team.

Many Thanks
Eddie

jluebbers · November 3, 2022 9:42PM

Thanks Eddie - I voted on the UserVoice page, for clarity here's the comment I put there:

This should be trivial to fix: whatever SQL query SQL Search is emitting to enumerate databases on an instance just needs to have a condition added to its WHERE clause to check whether the current user has access to a given DB, eg:

...WHERE has_dbaccess(name) = 1

or similar.

Eddie D · November 7, 2022 5:27PM

Hi jluebbers,
I have some further information you maybe pleased to hear.

The Development Team responsible for SQL Search are in the process of adding a fix for this situation. I do not have an accurate ETA but the team plan to release this fix sometime next week.

I will update this post, when I have information that the new version has become available

Many Thanks
Eddie

jluebbers · November 15, 2022 10:31PM

Thanks Eddie - please let us know when the fixed release is available.

jluebbers · December 12, 2022 11:15PM

Hi Eddie - any update on when the fixed version will be released?

iokevins · March 9, 2023 11:21PM

How does SQLSearch_3.6.1.3286.exe released on February 21, 2023 resolve this issue (if at all)? Thank you

Eddie D · March 20, 2023 11:24AM

Hi,
To answer your question:

How does SQLSearch_3.6.1.3286.exe released on February 21, 2023 resolve this issue (if at all)? Thank you

According to the SQL Search V3.6.1 releases notes does include fix for the problem, where it stops the attempted indexing of databases that the current user has no access to.

Do you still experience the reported problem?

Many Thanks

Eddie

iokevins · March 20, 2023 7:04PM

Thank you, I'll re-install.

Stop SQL Server from trying to login to all databases on the instance

Answers

Product Learning

Community Forums

Events & Friends

Simple Talk