1

I am attempting to use pymssql to connect to a client's database, but have thus far been unable to succeed. I believe that the root of the problem is this entry in the stack trace of my TDSDUMP:

dblib.c:761:dbsetlname(0x1ac3e10, <username>@<servername>.database.windows.net, 2) dblib.c:7929:dbperror((nil), 20042, 0) dblib.c:7981:20042: "Name too long for LOGINREC field"

One problem is that <servername>.database.windows.net already exceeds the string limit (which seems to be 30 according to this: How to use PHP's dblib PDO driver with long usernames? / SQLSTATE[HY000] Name too long for LOGINREC field (severity 2)).

I have also attempted to exclude the @<servername>.database.windows.net portion in the username entry only to receive the following error:

msgno 40532: "Cannot open server "1433D" requested by the login. The login failed."

According to https://github.com/pymssql/pymssql/issues/330 the @<servername> portion is requested by the server.

So at this point, I attempted to do the following:

user = username + "@{}".format(server)
user = user[:30]

And I received the following information (which was a slight improvement but still not ideal, given that I could still not establish the connection):

"Cannot open server "<server_name minus the last 6 characters>" requested by the login. The login failed."

If possible, I would prefer passing some parameter to pymssql's connect method that would override this character limit or do something to append the server to the username on the backend after calling dbsetlname on just <username> without the @<servername> portion. Does anyone have any recommendations (again the preference is to not ask the client to change our username, but we may have to resort to that or trying to use pyodbc if there's no other option).

These are a few of the connection methods I have tried:

conn = pymssql.connect(
host=host,
database=database,
user=username,
password=password,
port=int(port) # this is 1433
)

conn = pymssql.connect(
host=host,
database=database,
user=username + '@{}'.format(servername),
password=password,
port=int(port) # this is 1433
)



conn = pymssql.connect(
server=<servername>.database.windows.net,
database=database,
user=username,
password=password,
port=int(port) # this is 1433
)

Thanks in advance for any help/advice that you may be able to offer!

Community
  • 1
  • 1
JPope2014
  • 161
  • 1
  • 10
  • Can you include the `pymssql.connect()` method you're using to connect? – FlipperPA Mar 07 '17 at 11:40
  • Added as requested! – JPope2014 Mar 07 '17 at 15:48
  • Huh, I've never seen SQL Server require this. I'm not sure if there's something specific to SQL Server you need; perhaps try pyodbc with FreeTDS instead of pymssql with FreeTDS? That's the stack I use, and it is now officially support by Microsoft. – FlipperPA Mar 07 '17 at 20:22
  • @FlipperPA - I know that Microsoft now officially supports pyodbc but I just assumed that it was in conjunction with their own ODBC driver, not FreeTDS_ODBC. Am I mistaken? Does Microsoft officially support FreeTDS_ODBC, too? – Gord Thompson Mar 09 '17 at 23:51
  • Have you tried explicitly specifying version 7.3 of the TDS protocol to see if that helps any? – Gord Thompson Mar 10 '17 at 00:01
  • @GordThompson , yeah, I actually tried 7.3, 7.0, 7.1, whatever the default is, and also 8.0 :/. It seems odd that pymssql would impose a length restriction client side that is tighter than what is imposed by the server itself. I guess I could, in theory, follow the stacktrace for the error back in C and modify that, then rebuild pymssql? But we'd really like to not make any modifications to the base package either if we can help it. It could be that, like you guys are saying, pymssql with FreeTDS just isn't the right tool for the job. – JPope2014 Mar 10 '17 at 15:09
  • 1
    *"It seems odd that pymssql would impose a length restriction"* - [This comment on GitHub](https://github.com/pymssql/pymssql/issues/325#issuecomment-142386389) suggests that it might be due to the specific API that pymssql uses to work with FreeTDS. – Gord Thompson Mar 10 '17 at 15:41
  • 1
    According to Microsoft's blog post, they'll support pyodbc and django-pyodbc-azure with SQL Server. I still use FreeTDS with pyodbc and it has performed very well. – FlipperPA Mar 10 '17 at 20:36
  • Thanks Flipper and Gord for your help with this. When the client shortened the username, we still couldn't connect because of an issue with the encryption and a problem with pymssql (wrong FreeTDS version and couldn't override it), so I ended up using pyodbc after all. It actually worked really well, the only aggravating part is that we have to distribute the odbc.ini file with our driver config in it to be sure that the FreeTDS driver is available, but this problem is very small, especially given the issues that we were having using pymssql. – JPope2014 Mar 27 '17 at 16:24

1 Answers1

0

You can probably use an alternate connecting string method.

If you're using SQL Server Auth, try this:

conn = pymssql.connect(
    server="<servername>.database.windows.net",
    port=1433,
    user="username",
    password="password",
    database="dbname"
)

If you're using Windows Auth:

conn = pymssql.connect(
    server="<servername>.database.windows.net",
    port=1433,
    user="DOMAIN\USERNAME",
    password="password",
    database="dbname"
)

Have you tried either of these connect methods?

FlipperPA
  • 13,607
  • 4
  • 39
  • 71
  • Hey, I appreciate this suggestion, but I had already tried this shortly after submitting this question actually. It unfortunately still returned the 1433D. – JPope2014 Mar 07 '17 at 15:30