Every time I click the "New Query" button, or drag a file into the editor, the system prompts me for authentication. This time and time again leads to executing commands on the wrong database, and the only workaround is basically to accept the necessity to hit the enter key when prompted for auth, and to always double-check which database you're about to execute your query against. If the user account you are authenticating as has access to multiple databases, then you always have to change the database after creating a new window.
I am sure I'm not the only one very frustrated with this tool. Has anyone found a real workaround
New Query button -- always prompts for authentication
aldone
thanks for the information on this.
here is what I did, I created a database called 'demo' and made the user 'sample' the owner of the database and also set 'demo' to be the default database for this user. I opened the query useing the "Open File" button and ran the query, after this was completed I went to the "Open File" button again and selected the next query that needed to be run and it asked for authentication. I had checked remember password, but this does not seem to matter.
Any other ideas
Thanks again,
Bob
Autobase
Sorry, forgot the last bit...
< xml version="1.0" encoding="utf-8" >
<Export serverType="8c91a03d-f9b4-46c0-a305-b5dcc79ff907">
<Server name="usildlapqpub05" description="Local instance - 'usildlapqpub05'">
<ConnectionInformation>
<ServerType>8c91a03d-f9b4-46c0-a305-b5dcc79ff907</ServerType>
<ServerName>usildlapqpub05</ServerName>
<AuthenticationType>0</AuthenticationType>
<UserName />
<Password />
<AdvancedOptions />
</ConnectionInformation>
</Server>
</Export>
pavlo_ua
Chad, thanks for the answer. However, it doesn't really help. Let me give a repro scenario:
Context or not, this is really confusing and tough to use. If I'm in a query window for database X, pressing Ctrl-N should logically assume I'm trying to use the exact same credentials for the new connection. It doesn't seem to do that.
Thus, my question stands. Is there a way to make the studio not prompt for auth credentials if I've already given these credentials ten times in the same application session.
ALEXXX
I have similar issues - where I have template scripts saved in a file directory. Every time I open them I am prompted for a connection. I can live with that, but just this week it started to run into max connection problems - (I have 6 templates and max conns was set to 4). I changed max conns to '0' but it still persists. Screwy part of this is that I had not seen these connection errors for over a month.
Why does the tool require a new connection to open queries from script Did QA 2000 do this in the background I've never had a max connections issue anywhere I've done SQL work, so I was stumped by this. Anyone have some suggestions to change this behavior
Nick Poulis
i am also running into this problem. it is frustrating when you want to run multiple queries in a row and have to authenticate each time you open a new query file. In Query Analyzer, it would just open the query file in the same window and if you made changes to the previous query, it would ask to save or cancel.
Any other ideas
Mundru
I do have the same problem with dragging files into the Query Editor.
Although I am authenticated and have selected a database, I have to do that again when I drag and drop another file in the editor. I found this very, very annoying, this worked perfect in SQL 2000 Query Analyzer.
Is there some solution
sjbates101
SQL Server 2005 Standard.
Received this from the Release party for SQL Server and VB.NET.
All from the about window:
Microsoft SQL Server Management Studio 9.00.1399.00
Microsoft Analysis Services Client Tools 2005.090.1399.00
Microsoft Data Access Components (MDAC) 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)
Microsoft MSXML 2.6 3.0 4.0 5.0 6.0
Microsoft Internet Explorer 6.0.2900.2180
Microsoft .NET Framework 2.0.50727.42
Operating System 5.1.2600
Registered server information:
< xml version="1.0" encoding="utf-8" >
<Export serverType="8c91a03d-f9b4-46c0-a305-b5dcc79ff907">
<Server name="demo" description="">
<ConnectionInformation>
<ServerType>8c91a03d-f9b4-46c0-a305-b5dcc79ff907</ServerType>
<ServerName>demo</ServerName>
<AuthenticationType>1</AuthenticationType>
<UserName />
<Password />
<AdvancedOptions>
<DATABASE>demo</DATABASE>
<CONNECTION_TIMEOUT>15</CONNECTION_TIMEOUT>
<EXEC_TIMEOUT>0</EXEC_TIMEOUT>
<PACKET_SIZE>4096</PACKET_SIZE>
<ENCRYPT_CONNECTION>False</ENCRYPT_CONNECTION>
</AdvancedOptions>
</ConnectionInformation>
</Server>
</Export>
Wurschti
Hi Ilya. When you hit the new query button, it is context sensitve. Whatever you have selected in either the Registered Servers or Object Explorer panes will be what the new query will be opened against by default. For example, if you have the database 'DatabaseA' selected in Object Explorer on 'ServerA' using Windows Authentication, when you hit new query (or hit Ctrl+N), the new query will be opened on ServerA, DatabaseA, using your Windows authentication. If you have nothing selected at all, you'll get prompted. If you have something selected that is either not running, or you don't have access to, Management Studio will try to connect, fail, and then prompt you.
If you have NOTHING connected in your registered servers or object explorer, you'll get prompted as well.
HTH
usama galal
I am going to reply as well as I am having the same isssues.
SQL Server 2005 Enterprise Ed.
Microsoft SQL Server Management Studio 9.00.1399.00
Microsoft Analysis Services Client Tools 2005.090.1399.00
Microsoft Data Access Components (MDAC) 2000.086.1830.00 (srv03_sp1_rtm.050324-1447)
Microsoft MSXML 2.6 3.0 4.0 6.0
Microsoft Internet Explorer 6.0.3790.1830
Microsoft .NET Framework 2.0.50727.42
Operating System 5.2.3790
SVK
This is not a solution, per se, but it does ease the irritation a little when manually executing queries, especially simple ad hoc queries.
If you open a query editor window to the desired database, you can enter multiple queries in that one editor, and by selecting the text the interface intelligently executes only the highlighted lines. It avoids the multiple windows, and is fairly reliable.
Having said that, I also have a minor workaround I use on the main development database here. By making the database I most often use the default for my logon, the Studio allocates me to that database by default, and the only time I have to authenticate is when I access some of the secondary databases or start-up the Studio. Beyond that, it avoids most of the scripting to the wrong database grief.
As well, I have found that if you use a solution/project that defaults to a database, the interface seems to remember the database and never asks for re-authentication. Conceivably, you could have multiple solutions that have different connections and it would remember the connection...though I've never had need to test that surmise.
Dukeboss
Sorry for the delayed response, been on vacation.
Not sure what's different with our configurations guys, but mine works just as it 'should', i.e. as I described above. If I go through the exact steps you have been describing above, I never get prompted for credentials.
Could you give me some more information like:
- What version of SQL Server are you connecting to and what edition
- Open Management Studio, click on Help...About, then click the 'Copy Info' button and then paste the information in a post
- Right click on the registered server node, then click export. Export the given node's information to a file, then open the file in notepad (it's just XML) and paste the information here (remove any passwords please :-))
Maybe I can figure something out from that information,
KiriTheKid
This issue has been fixed in SP2.
In SP2, SQL Server Management Studio prompts for connection information much less frequently. Basically, it uses the same connection as whatever window you had focussed when you launched the editor. You can still change the connection whenever you like by right clicking in the editor and selecting "Connection > Change Connection" in the context menu.
Best regards,
Steve