Can't connect to database

jeremysimmons

Using SQL Authentication in both

I have the following in my log file
"C:ProgramDataRed GateLogsDLM DashboardRedGate.SQLLighthouse.WebServer-1.2.2.352-20150317-S-1-5-20.log"

RedGate.SQLLighthouse.SQLServerMonitoringService.ChangeNotification.SqlServerUnavailableException: Unable to snapshot database ---> System.Data.SqlClient.SqlException: The SELECT permission was denied on the object 'sysobjects', database 'mssqlsystemresource', schema 'sys'.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader()
at RedGate.SQLCompare.Engine.LiveDMVModel.ID()
at RedGate.SQLCompare.Engine.DatabaseFactory.PopulateObjectNames()
at RedGate.SQLCompare.Engine.DatabaseFactory.Populate(DBPopulationOptions popOptions)
at RedGate.SQLCompare.Engine.Database.Register(ConnectionProperties connectionProperties, Options options, IDmvModelFactory factory)
at RedGate.SQLLighthouse.SQLServerMonitoringService.Snapshotting.SqlCompareDatabaseCreator.DatabaseFromDbConfig(DbConfig dbConfig)
at RedGate.SQLLighthouse.SQLServerMonitoringService.Snapshotting.RavenCheckingSnapshotCreator.GetSnapshotOfDatabase(DbConfig dbConfig)
--- End of inner exception stack trace ---
at RedGate.SQLLighthouse.SQLServerMonitoringService.Snapshotting.RavenCheckingSnapshotCreator.GetSnapshotOfDatabase(DbConfig dbConfig)
at RedGate.SQLLighthouse.SQLServerMonitoringService.Snapshotting.SnapshotTakingObserver.TakeASnapShot(DbNeedsCheckingEvent sqlServerTransaction)

Chris.Allen

Can you give that user sysadmin?

jeremysimmons

yes, I will try that.
I feel that hardly qualifies as a solution though as it really violates the principle of least privilege.

Rob C

Hi Jeremy,

The account that DLM Dashboard uses to connect to the database needs to have the following permissions -

• the VIEW DEFINITION permission on all objects in monitored databases
• the SELECT permission for the system view sys.sql_expression_dependencies
• if you have encrypted objects, sysadmin and VIEW SERVER STATE permissions

So unless you have encrypted objects, you don't necessarily need to make the user sysadmin - as long as the user has VIEW DEFINITION for all the objects and SELECT for sys.sql_expression_dependencies.

Does that help?

jeremysimmons

Granting the sql login sysadmin membership before adding the server worked.

I don't consider that a solution.

Knowing EXACTLY the minimum set of privileges required for a login would certainly be nice.

Rob C

Granting sysadmin will work, but it's not necessary unless you have encrypted objects.

The minimum permissions for DLM Dashboard are as per my previous post. Our documentation page has a bit more detail on the subject - http://documentation.red-gate.com/displ ... quirements

Does that help?