2

I'm trying to setup a SQL Server 2016 installation in AWS EC2 and can't seem to get the security group settings right. Please note the "white dot" in the screen cap below - I am unable to administer the agent when connecting from my laptop.

Imgur

I've tried allowing inbound on 1434 in the security group, but no luck so far. I'd like to be able to stop/start the agent without needing to RDP into the server. It is worth noting that the "Laptop" behavior occurs even from other hosts inside the same security group.

The host is Windows Server 2012. I have also added a custom Windows Firewall rule allowing 1434 to listen. Netstat -a shows...

Proto  Local Address          Foreign Address        State
...
TCP    127.0.0.1:1434         hostname000:0          LISTENING
...

How do I remote administer the SQL Server Agent in this environment?


I had one of my DevOps guys confirm port 1434 is fully open; however the Server Agent behavior is unchanged.

I'm perfectly able to administer the service. But I need to RDP into the VM to do so. I'd like to have the quick visual indicator of service status & have the option to start/stop/restart from object explorer in a laptop session.

I logged in as sa for the purposes of the screencap. The behavior is consistent when logged in with sysadmin domain credentials.

I am able to connect via SSMS just fine. SQL Agent has no indicator however when I'm connected via SSMS running anywhere except local to the instance. From brief googling, it seemed like this may be similar to the "cannot connect to the DAC" issue to which teh interwebz suggested port 1434 config issues might be the root cause. Apparently that's not the cause so... to StackExchange for help!

  • 2
    Wait, is the problem that you can't administer the service, or just that Object Explorer doesn't show you the current service status? (For Azure SQL DB and other similar services - I've never been able to determine status either, I don't think you can do that over TCP/UDP. At least in the current versions of SSMS v17 there is a more intuitive ? to indicate inability to determine service status.) – Aaron Bertrand May 12 '17 at 21:26
  • I would contact EC2 support. I'm not sure exactly what functionality they intend to expose via Object Explorer but I know some of that is intentionally blocked by Azure (or simply not available over the supported protocols). I don't know enough about the Amazon scenario but I bet Amazon does! :-) – Aaron Bertrand May 12 '17 at 21:32
  • Can you open the "Services" control panel and connect to the server? – Daniel Hutmacher May 15 '17 at 14:10
  • There's 9 pages of suggestions here. Many red herrings but a few things to try: sqlservercentral.com/Forums/825246/… In summary: compare login account, SSMS version, run as administrator. Also maybe run exec msdb..sp_help_job and confirm it's not a permissions thing. Also to clarify, you can expand the node and see jobs?, Are start/stop functions available or greyed out? – Nick.McDermaid May 15 '17 at 22:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.