Cannot log in to new SQL server

Jose C 96 Reputation points
2021-06-24T14:46:47.96+00:00

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)

------------------------------

For help, click: https://learn.microsoft.com/sql/relational-databases/errors-events/mssqlserver-2-database-engine-error

------------------------------

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.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,696 questions
0 comments No comments
{count} votes

Accepted answer
  1. Jose C 96 Reputation points
    2021-06-25T00:16:55.177+00:00

    Erland, that's exactly what I did. Ugh, lesson learned. Soon as I installed SQL standard 2019, agent worked just fine.

    Thanks for all of your help guys!


2 additional answers

Sort by: Most helpful
  1. Ronen Ariely 15,096 Reputation points
    2021-06-24T16:32:14.243+00:00

    Good day,

    The server was not found or was not accessible. Verify that the instance name is correct

    let's focus on the above message and in anyway, before dealing with the Agent you must first solve the issue of connecting the server.

    (1) Check if the SQL Server service is running.

    Open the list of services by execute the command: services.msc
    In the list of services try to find the service with the name SQL Server (<here you should see the instance name - copy this name for next step>)

    (2) Now if the SQL Server service does not exists then you did not installed the server. If the service is running then we can try to connect using the instance name which you copied in step 1

    Using SSMS fill the connection information:
    * Server type: Database engine
    * Server name: .\<use the instance name from step 1>
    * Authentication: Windows Authentication (assuming you installed the server as administrator and you added the user to the server admin)

    (3) If you still have issue then go over this document: https://learn.microsoft.com/en-us/sql/ssms/quickstarts/ssms-connect-query-sql-server?view=sql-server-ver15

    (4) Please inform us if you still have issue, and in this case explain what you did/got in these steps

    0 comments No comments

  2. Erland Sommarskog 100.9K Reputation points MVP
    2021-06-24T21:19:07.823+00:00

    I would double-check that you really installed Standard Edition. What you describe sounds very much as you have installed Express Edition, which does not support Agent.

    For the connection problem, since you have a named instance, you need to supply the instance name, that is HOSTNAME\SQL2019STD.

    0 comments No comments