How can I find the Connection String

[expired user #10217]'s profile image [expired user #10217] posted 7 years ago in General Permalink

I am using Heidi to connect to an SQL database. The connection works since I can connect to the DB.

I need to know the connection string.

Where can I find the Connection info, in Heidi?

Thanks, Mathijs

ansgar's profile image ansgar posted 7 years ago Permalink

The connection string for MSSQL connections is only internally formed and used in HeidiSQL. Using the session manager is the right way to change parts of this connection string, but again - the connection string is nowhere displayed. One reason for not being displayed is that it contains the cleartext password, and it would be a security risk to show it somewhere.

[expired user #10217]'s profile image [expired user #10217] posted 7 years ago Permalink

Hi,

thanks for the clarification. I need the connection string for some database testing. Is there another approach to get the connection string? Obviously it's OK if the cleartext password part of the connection string is masked (made unreadable).

ansgar's profile image ansgar posted 7 years ago Permalink

Is there another approach to get the connection string?

Not with HeidiSQL I'm afraid.

[expired user #10217]'s profile image [expired user #10217] posted 7 years ago Permalink

Did some additional research, I found there is an (easy) approach to get the connection string.

It's described in just a few steps here: https://teusje.wordpress.com/2012/02/21/how-to-test-an-sql-server-connection/

Also, I was told there is a possibility to get it from MS Visual Studio as well:

  1. Open the Server Explorer window in Visual Studio.
  2. Right-click the Data Connections node and select Add Connection.
  3. Select Microsoft SQL Server as Data Source and .NET Framework Data Provider for OLE DB as Data provider. You'll then need to establish a connection - select the server, provide valid credentials and select your database.
  4. When the connection is established, you can select you database on the list and see the Connection String value in the Properties tab (see the attached screenshot).

Hopefully this info help others as well. Also, I hope this feature will be implemented in Heidi SQL as well (somewhere in DB / Connection properties info window for example)...

1 attachment(s):
  • server_explorer
ansgar's profile image ansgar posted 7 years ago Permalink

Does that connection string contain the clear text password?

[expired user #10217]'s profile image [expired user #10217] posted 7 years ago Permalink

In this case, no.

[expired user #10217]'s profile image [expired user #10217] posted 7 years ago Permalink

Could I "upgrade" this question to a feature request, so that HeidiSQL will support displaying the Connection String?

Note: if clear text passwords are displayed (I believe this is possible in some cases), these should be blurred , of course.

ansgar's profile image ansgar posted 7 years ago Permalink

What about expanding an already contained feature: Hovering the mouse over the status bar panel with the server version on it shows a tooltip with some connection related information. The connection string is not yet contained, but that could be the first addition to add it (while clearing out the password). Secondly, as a long connection string gets unreadable in a tooltip, a click on that panel would be nice to bring up a dialog with the same information, but more readable.

kalvaro's profile image kalvaro posted 7 years ago Permalink

If I understood correctly, the connection string is not a server-side token you need to retrieve in order to connect or anything remotely similar. It's just the syntax used by some Microsoft connectors to configure the connection (because they apparently couldn't figure out a way to just set the values separately with properly named parameters) and the programmer composes it to suit his needs. The server has no way to know what client libraries or other parameters you want to use. Certainly, it can be provided as debug information (just like many Java-powered programs display the JRE version) but that "I need to know the connection string" statement confuses me.

[expired user #10217]'s profile image [expired user #10217] posted 7 years ago Permalink

@ansgar your suggestion sounds perfect to me.

Code modification/commit 53b4ab6 from ansgarbecker, 7 years ago, revision 5145
Include ADO connection string in status bar connection details, shorten the lines reasonably and show the full lines in a message box when clicking on it. See http://www.heidisql.com/forum.php?t=23049
ansgar's profile image ansgar posted 7 years ago Permalink

r5145 now shows the shortened connection string in the status bar hint, and the full infos in a message box when clicking on it.

[expired user #10217]'s profile image [expired user #10217] posted 7 years ago Permalink

that sounds perfect! thanks very much for the anticipation on user feedback

[expired user #10217]'s profile image [expired user #10217] posted 7 years ago Permalink

Hi, the connection string info is now displayed in a message box, BUT... it can't be selected (copy/paste).. since it's quite a long string to type it over, that's pretty annoying... :(

It is possible to make all the info on the message box copy/paste-able?

[expired user #10217]'s profile image [expired user #10217] posted 7 years ago Permalink

Also, I noticed there that there is a potential "bug" in the Connection string info, as it is displayed in the message box. Having Heidi's session manager set to use Windows authentication to connect to the database (see first screenshot) (which is definitely not root), I connected to the db successfully, followed by checking the connection string in the message box, displaying "user Id=root), made my eyebrows frown...

And also, I noticed that the Initial Catalog (database name) is missing.

While also (more reduntant) info is displayed which typically is not of any value in the connection string..

To explain it a little: the correct Connection string for the environment I connect to is this: Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=CreditManagementServices;Data Source=[HIDDEN]

Some of the above mentioned info (Initial Catalog) is not being displayed in the Connection String currently reported by Heidi...

2 attachment(s):
  • 2017-01-20_15-19-05
  • 2017-01-20_15-14-21
ansgar's profile image ansgar posted 7 years ago Permalink

In a message box, you can press Ctrl+C to copy its content.

The user id in the connection string is taken from your parameters. If it's different from that, it's likely that the server modified the connection string here. I have also seen some parameters which I definitely do not set via HeidiSQL.

[expired user #10217]'s profile image [expired user #10217] posted 7 years ago Permalink

Thanks for the tip. Didn't know that it's possible to copy message boxes.

It would be very usefull if the database name (Initial Catalog) could be added to the connection string (and maybe other more unnecessary be removed).

ansgar's profile image ansgar posted 7 years ago Permalink

Well you wanted the connection string, and now you call some of its parameters unnecessary. I think it's difficult to tell between parameters which are necessary and not, when you ask more than one user.

[expired user #10217]'s profile image [expired user #10217] posted 7 years ago Permalink

yeah well I wanted the connection string, including database name (Initial Catalog)... thanks for all the effort so far and no offence!

tele's profile image tele posted 2 years ago Permalink

Hey, I noticed that this feature was either moved or removed. Can you direct me to where i may be able to find the connection string? I am able to connect to my db over the tunnel I created in Heidi, but not using my C# program.

Thanks

ansgar's profile image ansgar posted 2 years ago Permalink

No, the connection string of a MS SQL connection is still visible, when you click on the status bar panel with the version info:

Description

Other modes like MySQL, PostgreSQL and SQLite don't have (and never had) a connection string there.

cheese62's profile image cheese62 posted 2 years ago Permalink

Is there a consideration that this might be available, at some point, for MySQL? The reason I ask is that HeidiSQL allows me to import a csv file but LOAD LOCAL DATA FILE doesn't within my php page, using the exact same query from the window on Heidi.

So I was hoping t examine any differences between your connection string and mine to see what I have got wrong.

Thanks in advance for any thoughts.

ansgar's profile image ansgar posted 2 years ago Permalink

For MySQL there is just no connection string in HeidiSQL, like for MS SQL. In MySQL mode, HeidiSQL uses the C-API and passes the session parameters to functions like mysql_options() and mysql_real_connect().

Regarding that LOAD LOCAL DATA command, HeidiSQL enables the MYSQL_OPT_LOCAL_INFILE option when initializing a connection. That may be important for your PHP driver.

Please login to leave a reply, or register at first.