Unable to connect to MSSQL via web2py - SSL Security Error

[Ryan Hood]

Hi,

I am having trouble connecting to a SQL Server Instance via web2py.  I tried connecting to three different SQL Server instances (all 2014) on three different servers on three different instances of web2py.

Here is the full traceback:

Traceback (most recent call last):
  File "/home/mdipierro/make_web2py/web2py/gluon/restricted.py", line 220, in restricted
  File "C:/Users/Administrator/Downloads/web2py_win/web2py/applications/stoptaneous/models/db.py", line 21, in <module>
    db = DAL('mssql://api:hotshot22@WIN-K5BSFDVN3KM/GTFSChicago')
  File "/home/mdipierro/make_web2py/web2py/gluon/dal.py", line 7867, in __init__
RuntimeError: Failure to connect, tried 5 times:
Traceback (most recent call last):
  File "/home/mdipierro/make_web2py/web2py/gluon/dal.py", line 7845, in __init__
  File "/home/mdipierro/make_web2py/web2py/gluon/dal.py", line 688, in __call__
  File "/home/mdipierro/make_web2py/web2py/gluon/dal.py", line 3454, in __init__
  File "/home/mdipierro/make_web2py/web2py/gluon/dal.py", line 648, in reconnect
  File "/home/mdipierro/make_web2py/web2py/gluon/dal.py", line 3452, in connector
Error: ('08001', '[08001] [Microsoft][ODBC SQL Server Driver][Shared Memory]SSL Security error (18) (SQLDriverConnect); [01000] [Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen (SECCreateCredentials()). (0); [01S00] [Microsoft][ODBC SQL Server Driver]Invalid connection string attribute (0)')

For what it is worth, I can connect via pyodbc and the following code runs just fine for me (aliased to protect the innocent) and displays the expected results.

import pyodbc
cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=localhost;DATABASE=testDB;UID=testUser;PWD=password')
cursor = cnxn.cursor()
cursor.execute("select * from sample_table")
rows = cursor.fetchall()
for row in rows:
  print row

Here is my connection string in web2py:

db = DAL('mssql://testUser:password@localhost/testDB')

In addition, I worked on a project roughly a year ago and a colleague was able to connect to our SQL Server instance just fine (2008 r2 instance) using web2py.  Examined his code and it looks identical to this with the references changed.  Perplexed at this point.  Any advice is greatly appreciated.

Best,

Ryan

[Niphlod]

uhm. connecting to "localhost" and from outside can require very different handshakes. For what is worth, I work daily with 2014 and I can connect fine (as long as the odbc driver is there). Your error seems to pinpoint to SSL security needed to connect to that host.

Before jumping in that ship, however, there's a thing you can try:

mssql://DRIVER={SQL Server};SERVER=localhost;DATABASE=testDB;UID=testUser;PWD=password

if you can connect from "pure" pyodbc, than this will use EXACTLY the same method to connect with the DAL.


PS: if you're using 2014, you can benefit from the mssql4:// that improves performances with pagination.

[Ryan Hood]

Thanks for the advice and for sharing your experiences.  Also appreciate the mmsql4 tip.  So this will probably surprise you, but I still get the same SSL error when I switch to this connection string: mssql://DRIVER={SQL Server};SERVER=localhost;DATABASE=testDB;UID=testUser;PWD=password.

I again tested the code above with pure pyodbc and that still works fine. 

[Willoughby]

Login errors can be seen in the event viewer too IIRC, you might check there for clues.

[Niphlod]

that's indeed very strange. What driver is DAL using ? (you can see it even when you start web2py as a "welcome" message in the cmdline)

[Ryan Hood]

Here is my welcome message:

[Ryan Hood]

Thanks for posting.  No failed logins were recorded in the event viewer after I got this error.  I was able to see failed logins previously in the event viewer but they were unrelated to the SQL Server Connection attempt.

[Alen Cerovic]

is there resolution to this? I am having similar errors, tried on diferent computer with another sql server same problems

db = DAL('mssql://sa:pass...@127.0.0.1\InstanceName/DbName')

Error: ('08001', '[08001] [Microsoft][ODBC SQL Server Driver][DBNETLIB]SSL Security error (18) (SQLDriverConnect); [01000] [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (SECCreateCredentials()). (0); [01S00] [Microsoft][ODBC SQL Server Driver]Invalid connection string attribute (0)')

or

db = DAL('mssql://DRIVER={SQL Server};SERVER=127.0.0.1\InstanceName\DbName,1433;UID=sa;PWD=password')

Error: ('08001', '[08001] [Microsoft][ODBC SQL Server Driver][DBNETLIB]SSL Security error (18) (SQLDriverConnect); [01000] [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (SECCreateCredentials()). (0)')

[Niphlod]

I know I'm asking a strange thing, but are you using web2py binaries or source code ?
If you're using binaries, can you please stop web2py, go into the root, rename secure32.dll to secure32.dll.old and then try again ?

On Wednesday, September 24, 2014 5:52:43 PM UTC+2, Alen Cerovic wrote:

is there resolution to this? I am having similar errors, tried on diferent computer with another sql server same problems

db = DAL('mssql://sa:pa...@127.0.0.1\InstanceName/DbName')

[Alen Cerovic]

Can t believe it, it did the trick :) yes I am using binaries on windows 7 64bit. Can you explain me what is going on, thnx

[Niphlod]

I believe it's a problem in bbfreeze. But I must do additional tests for it.

[Dallas Baird]

Hi all,

I was not having this problem last week, all of a sudden I am.

Today I had installed many SQL SERVER 2008 r2 libraries (integration services, ssms, dts backwards compatability etc).

Perhaps the new installation has introduced a versioning problem with a dll?


Dallas

[Dallas Baird]

On Thursday, 25 September 2014 17:49:44 UTC+10, Alen Cerovic wrote:

Can t believe it, it did the trick :) yes I am using binaries on windows 7 64bit. Can you explain me what is going on, thnx

FWIW: I didn't have this problem last week, but I do this week. Yesterday I installed Sql server integration services, client libraries, dts backwards compatability etc.. from the sql server 2008 r2 dvd. Perhaps this has introduced a new dll version?

Dallas