0

I am facing issues with connecting to a SQL Server database from Excel VBA. I primarily suspect that the error is occurring because of the incorrect handling of the password containing double quotes.

Scenario :

  • SQL Server Name - FOO\BAR
  • Initial Catalog - ScrollBar
  • User - Test_User
  • Password - tejg3kl!"

The connection string I am using is:

Public Const ConnectionStringONLINE = "Provider=SQLOLEDB.1;Data Source=FOO\BAR;Initial Catalog=ScrollBar;User Id=Test_User;Password=tejg3kl!"";"

An extra double quote is included in the Password value as an escape sequence character.

Code to connect to the database -

Dim DbConn As ADODB.Connection
Set DbConn = New ADODB.Connection  
DbConn.Open ConnectionStringONLINE

When the above code is executed, I receive an error -

Run-time error'-2147217843 (80040e4d)':

Login failed for user 'Test_User'.

I did an UDL test for the same credentials specified in the connection string and the connection worked fine.

Any suggestions on why the error is occurring in VBA? Do I need to modify the connection string?

Edit - Just changed the password string to include an exclamation mark before the double quotes to make it appear exactly like the real password that I am using.

Community
  • 1
  • 1
StarDotStar
  • 2,865
  • 3
  • 20
  • 15
  • Does the password actually contain `""`? Your connection string as it stands would cause a syntax error ... – Alex K. Jan 09 '15 at 17:45
  • Alex - The password is exactly this tejg3kl" – StarDotStar Jan 09 '15 at 17:47
  • Check this: http://www.connectionstrings.com/sql-server-native-client-11-0-oledb-provider/ – Maciej Los Jan 09 '15 at 17:48
  • 1
    Sorry, I misread. Try it quoted: `"Provider=SQLOLEDB.1;Data Source=FOO\BAR;Initial Catalog=ScrollBar;User Id=Test_User;Password=""tejg3kl"""";"` – Alex K. Jan 09 '15 at 17:50
  • Maciej - Thanks for the link. But I was unable to find anything related to handling passwords containing double quotes in the connection string – StarDotStar Jan 09 '15 at 17:51
  • Alex - I used the connection string that you provided. But there's a run-time error that says "Format of the initialization string does not conform to the OLE DB specification " In the debug mode, the connection string looked like this - "Provider=SQLOLEDB.1;Data Source=FOO\BAR;Initial Catalog=ScrollBar;User Id=Test_User;Password="tejg3kl"";" – StarDotStar Jan 09 '15 at 17:59
  • Try changing the password or use a different user (at least temporarily) so that you can remove the double-quote from the equation entirely. Once you get it working with another user or password, then you can concentrate on dealing with the double-quote. – mwolfe02 Jan 09 '15 at 18:07
  • mwolfe02 - That will be the last resort. I will have to contact the database admin team on Monday to get the password changed. But I am more interested in whether this case can be handled in VBA. – StarDotStar Jan 09 '15 at 18:12
  • @StarDotStar, yeah, i think you need to try other connection string. Have a look at Spock's solution - looks promisingly ;) – Maciej Los Jan 09 '15 at 18:26
  • Are you sure you have the correct credentials? Your exact code (with a different server name) works for me for a login set to the same password, against SQL Server 2008R2. – Geoff Jan 09 '15 at 18:30
  • Geoff - I did not put the original details for obvious reasons, but the connection string that I have mentioned in the question above resembles the original one exactly except that the exact password also contains an exclamation mark before the double quote, like this - tejg3kl!" I am not sure if that matters.. – StarDotStar Jan 09 '15 at 18:46
  • 1
    @StarDotStar - it works fine for me with the exclamation point too. I'm using the `Microsoft ActiveX Data Objects 2.0 Library` connecting to SQL Server 2008R2, from Excel 2013. – Geoff Jan 09 '15 at 18:58

2 Answers2

1

Well, this is really a weird one! After Geoff suggested in the comments that it worked for him with the double quotes, I took a closer look at my case.

It turns out that the password to the database was tejg3kl!” And the one that I was using in the code was tejg3kl!"

Did you notice the difference in the double quotes?

Apparently there are different types of double quotes as explained in this question - Are there different types of double quotes in utf-8 (PHP, str_replace)? and also here - http://unicode.org/cldr/utility/confusables.jsp?a=%22&r=None

So, finally all I did was, to use ” instead of " in the connection string and the code worked perfectly fine. (Also, there was no need for including any escape sequence)

Can someone please explain the need for different types of double quotes?

Community
  • 1
  • 1
StarDotStar
  • 2,865
  • 3
  • 20
  • 15
  • 1
    They are called curly/smart/pretty quotes, unlike the regular `"` there are different glyphs for opening and closing (so basically look nicer), see http://en.wikipedia.org/wiki/Quotation_mark#Curved_quotes_and_Unicode – Alex K. Jan 10 '15 at 15:57
0

escaping a double quote doesn't quite work that way in vb. You have to use the chr function to insert the quote into a string.
Try this. ..

ConnectionStringONLINE = "Provider=SQLOLEDB.1;Data Source=FOO\BAR;Initial Catalog=ScrollBar;User Id=Test_User;Password=tejg3kl" & Chr(34) & ";"
Spock
  • 4,700
  • 2
  • 16
  • 21
  • 1
    In fact, it does work that way; you can double-up a double quote inside a string to escape it. Example: `Const myString = "Try ""this"" on for size"` – Geoff Jan 09 '15 at 18:32
  • @StarDotStar - Right - your code is fine. Check your user credentials. – Geoff Jan 09 '15 at 18:34
  • But even this gave the same error - "Login failed for user 'Test_User'." This is despite the fact that the connection string looked correct at run-time : "Provider=SQLOLEDB.1;Data Source=FOO\BAR;Initial Catalog=ScrollBar;User Id=Test_User;Password=tejg3kl";" – StarDotStar Jan 09 '15 at 18:40