Hello,
I'm fairly new to installing SQL 2019 standard and am having two issues.
When I first installed SQL server 2019 standard on a server 2019 box, the problem I got was I could not run the SQL Server Agent.
When I go to SQL Server Configuration Manager, the state it is in is "stopped" and when I look at the start mode, it shows Other(Boot, System, Disabled, or Unknown)
When I try starting it, I get the following message:
"The SQL Server Agent (SQL2019STD) service on local computer started and then stopped. Some Services stop automatically if they are not in use by other services or programs."
I checked the SQL agent out log and this is what I get:
2021-06-24 10:37:45 - ? [508] Logging SQL Server Agent messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019STD\MSSQL\Log\SQLAGENT.OUT'.
2021-06-24 10:37:45 - ? [000] Event Global\sqlserverRecComplete$SQL2019STD opened
2021-06-24 10:37:45 - ? [100] Microsoft SQLServerAgent version 15.0.2000.5 (X64 unicode retail build) : Process ID 3936
2021-06-24 10:37:45 - ? [495] The SQL Server Agent startup service account is QEI\QEIWEB$.
2021-06-24 10:37:45 - ? [393] Waiting for SQL Server to recover database 'msdb'...
2021-06-24 10:37:45 - ! [298] SQLServer Error: 229, The EXECUTE permission was denied on the object 'sp_sqlagent_update_agent_xps', database 'msdb', schema 'dbo'. [SQLSTATE 42000] (DisableAgentXPs)
2021-06-24 10:37:45 - ! [000] The EXECUTE permission was denied on the object 'sp_sqlagent_update_agent_xps', database 'msdb', schema 'dbo'. [SQLSTATE 42000] (Error 229)
2021-06-24 10:37:45 - ! [298] SQLServer Error: 229, The EXECUTE permission was denied on the object 'sp_sqlagent_update_agent_xps', database 'msdb', schema 'dbo'. [SQLSTATE 42000] (DisableAgentXPs)
2021-06-24 10:37:45 - ! [000] The EXECUTE permission was denied on the object 'sp_sqlagent_update_agent_xps', database 'msdb', schema 'dbo'. [SQLSTATE 42000] (Error 229)
2021-06-24 10:37:45 - ? [098] SQLServerAgent terminated (normally)
the other issue I'm having is when I try logging in to SSMS, it says it cannot connect to the name of the server.
see below for the message:
Cannot connect to HOSTNAME. ( I masked the real name of the computer for security reasons)
===================================
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (.Net SqlClient Data Provider)
Error Number: 2
Severity: 20
State: 0
Program Location:
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling, SqlAuthenticationProviderManager sqlAuthProviderManager)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
at System.Data.SqlClient.SqlConnection.Open()
at Microsoft.SqlServer.Management.SqlStudio.Explorer.ObjectExplorerService.ValidateConnection(UIConnectionInfo ci, IServerType server)
at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()
===================================
The system cannot find the file specified
Can anyone please lead me to the right direction on this? I have a feeling it's the way I installed it and it wasn't done properly.