Management Studio keeps wanting to re-connect to server

  • I'm sorry if this has been asked before but its driving me mad.

    If, say, I right-click on a table in Management Studio and ask it to retrieve all records it throws up the Connect to Database Engine screen and I have to logon again. Why?

    I've clicked on a table so it must know what server/database I'm connected to. Why do I have to reconnect to the server I'm already connected to. Enterprise Manager didn't do this. Is there an option I'm missing?

    I have to say I've yet to see much in Management Studio, from a user interface view point, that is anything better than Enterprise Manager.

  • Are we talking about the 2008 version?

    Is this everytime, or after a period of inactivity?

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • 2008 and yes it's every time.

    I'm sure it's not an inactivity issue. I can try to select records from two tables one immediately after the other and get the logon screen for both.

    My colleague has excatly the same problem so I assumed it's just the default behaviour. I want it to behave sensibly.

    Thanks for your reply.

  • Tools --> Options --> Query Execution --> SQL Server --> Advanced

    There is a checkbox for "Disconnect after query executes". Is that one checked or not?

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • It wasn't so I ticked it but it didn't make any difference I'm afraid. I even closed MS and went back in just in case.

    Thanks for taking an interst in this. Can I assume that yours doesn't behave in this way?

    For your information I'm running MS on a Windows 7 64bit PC so have installed the 64bit version and patched it to SP3.

  • Peter Howard (2/10/2012)


    It wasn't so I ticked it but it didn't make any difference I'm afraid. I even closed MS and went back in just in case.

    Thanks for taking an interst in this. Can I assume that yours doesn't behave in this way?

    For your information I'm running MS on a Windows 7 64bit PC so have installed the 64bit version and patched it to SP3.

    Never experienced this behaviour myself.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • So it is doing for multiple users on the same database. What about trying a different server or different databases on the same server? Also, does it occur just when you select rows from a specific table or does it do it for any object? For example, if you try to script out a stored procedure?

    Also, are you logging in with a SQL account or a Domain account?

    Whatever the case this is not the default behavior.

  • We've got SQL 2000, 2005 and 2008 servers. We've also got Enterprise Manger (2000), MS 2005 and MS 2008.

    If I access any of the servers using MS 2008 I get the behaviour described.

    If I access the 2000 and 2005 servers using MS 2005 it works fine - doesn't want to reconnect.

    Obvioulsy with EM I can only access the 2000 server and it works fine.

    On all of them I'm connecting using SQL Server Authentication user sa.

    So the problem seems to be MS 2008. It's probably something I have/haven't done but I'll be bu..ered if I know what.

  • Sorry, didn't answer your questions.


    So it is doing for multiple users on the same database.

    Yes


    What about trying a different server or different databases on the same server?

    See original reply. Does this on any database on any server


    Also, does it occur just when you select rows from a specific table or does it do it for any object? For example, if you try to script out a stored procedure?

    Any object


    Also, are you logging in with a SQL account or a Domain account?

    See original reply.

  • Do you get any time-out warnings / errors messages?

  • No, nothing. Just goes straight to the logon screen.

    Don't know if this helps but when I first logon to MS 2008 I get a 'Connect To Server' screen. When I try to run a query I get a 'Connect to Datebase Engine' screen.

    Other than the title they look identical. The only difference is that on the 'Connect To Server' the Server Type field is enabled and on the 'Connect to Datebase Engine' it isn't.

  • This might be a shot in the dark but try the following:

    1. In the Server Registration view right-click on the server and select Properties.

    2. In the General tab change the authentication to SQL Server Authentication and add the sa information.

    3. Be sure to click on the "Remember Password" checkbox.

    The problem might be the fact that SSMS defaults to Windows Authentication when a server is registered.

  • Sorry, already shows SQL Server Authentication and sa.

    This must be something to do with queries. It only happens when I try to run a query either by right-clicking on a table and selecting all rows or when clicking on New Query. If I cancel the login it shows a query tab with 'not connected'.

    It doesn't happen if, say, I open the table in design mode.

  • It might be temporary issue. Did you try fresh SSMS window?

    Also, are you using VPN on Client system? It disconnects the local system from the network (default behaviour, but can be adjusted) so SSMS tries to reconnect on each attempt.

  • Now I'm going to sound thick.

    By SSMS I assume you mean SQL Server Management Studio. If I'm wrong please explain.

    By 'fresh SSMS window' do you mean closing and opening MS again? If so have done so loads of times.

    I'm not accessing the servers over a VPN. All local network. Can you please explain how I adjust the disconnect. That might have some legs to it.

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply