0

The SQL Server Management Studio is working just fine when we RDP into the server and try to perform any sa privileges.However, when we use it from our local machines it is throwing the errors

Situation:

SSMS 2016 client

Version: Microsoft SQL Server 2014- Service Pack 1 -Standard Edition

TCP/IP is also enabled.

I can connect successfully to the database server using management studio but when I try to open database diagrams, I get the below error. I don't even see SQL server agent even if we login with sa privileges.

SQL Server agent service is running.

  TITLE: Microsoft SQL Server Management Studio
  ------------------------------
   An exception occurred while executing a Transact-SQL statement or batch.(Microsoft.SqlServer.ConnectionInfo)
   ------------------------------
   ADDITIONAL INFORMATION:
   A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.) (Microsoft SQL Server, Error: 10054)
   For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=10054&LinkId=20476
  ------------------------------
  An existing connection was forcibly closed by the remote host
  ------------------------------
  BUTTONS:
  OK
Improve this question
2
  • This doesn't have anything to do with the error in the post, but " I don't even see SQL server agent even if we login with sa privileges." sounds like you aren't choosing to run SSMS as administrator.
    – Sean Gallardy
    Commented Mar 8, 2017 at 15:49
  • I'm running SSMS as administrator,still I can't see SQL Server agent. I use same SSMS tool to connect to other instance of SQLserver which is on same VM as the one I'm having trouble with, but this show me SQL server agent.
    – user119260
    Commented Mar 8, 2017 at 15:54

1 Answer 1

Reset to default
1

You say that by using RDP directly for logging onto and managing that server you obviously have enough rights to manage the server.

What is different is that you are now no longer using a direct connection?

  1. If there are more than two computers involved, then probably Kerberos needs to be configured.
  2. Are you sure that SQL Server Agent installed correctly. Check for installation errors in the Error Log.
  3. Have you manually tried starting or reconfiguring SQL Server Agent? Or used the Services panel to start SQL Server? Or run net start SQLSERVERAGENT.

If Kerberos is needed because of multiple hops between computers: Microsoft® Kerberos Configuration Manager for SQL Server® has some guidance on setting up and configuring Kerberos in your environment.

Improve this answer
1
  • I'm trying to log in using SQL server authentication and auth_scheme is showing as SQL when I ran sys.dm_exec_connections dmv. Yes, I manually restarted both SQL Server Agent and SQL Server service from service panel. I always use the SQL login which have sa privilege to connect to server. It worked fine for last two years but this error showed up after memory was upgrade on VM server recently.
    – user119260
    Commented Mar 9, 2017 at 14:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.