Can't connect to database

jeremysimmonsjeremysimmons Posts: 4
edited April 10, 2015 10:41AM in DLM Dashboard
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)
· Share on Twitter

Comments

  • Chris.AllenChris.Allen Posts: 579
    Can you give that user sysadmin?
    · Share on Twitter
  • jeremysimmonsjeremysimmons Posts: 4
    yes, I will try that.
    I feel that hardly qualifies as a solution though as it really violates the principle of least privilege.
    · Share on Twitter
  • Rob CRob C Posts: 419 Gold 2
    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?
    Software Engineer
    Redgate Software
    · Share on Twitter
  • jeremysimmonsjeremysimmons Posts: 4
    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.
    · Share on Twitter
  • Rob CRob C Posts: 419 Gold 2
    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?
    Software Engineer
    Redgate Software
    · Share on Twitter
Sign In or Register to comment.