4

I'm trying to connect to a Microsoft SQL Server / Microsoft Azure database with PHP's PDO:

<?php

// no actual login data, but similar string lengths
$dbHost = 'aa1234bbb5.database.windows.net';
$dbUser = 'db_a1a1a1a1_b2b2_c3c3_d4d4_e5e5e5e5e5e5_ExternalWriter';
$dbPass = 'pPAs0wOoO1&r#dd';
$dbName = 'db_a1a1a1a1_b2b2_c3c3_d4d4_e5e5e5e5e5e5';

try {
    $pdo = new PDO("dblib:host=$dbHost:1433;dbname=$dbName", $dbUser, $dbPass); 
} catch (PDOException $e) {
    echo "Failed to get DB handle: " . $e->getMessage() . "\n";
    exit;
}

The PDO initialization throws an PDOException with the following message:

SQLSTATE[HY000] Name too long for LOGINREC field (severity 2)

I'm running PHP 5.4.41-0+deb7u1 on Debian 7.7 x64.

My questions boil down to:

  1. Why do I get this error message?
  2. How should I actually connect to the database?

Note: I cannot change the login data because I need to access the backend database of a Microsoft Access Web App. If you create such a Web App, Microsoft creates the database on one of its "publicly available" Azure servers. You can ask the server to give you a username and a password - but unfortunately you have to use whatever is given to you.

Hauke P.
  • 2,695
  • 1
  • 20
  • 43
  • Hi Hauke,What driver are you using to connect to Azure SQL DB. And can you please elaborate on why you can not change your username/password. You should be able to login on Azure.com and change the credentials from the portal. Then you will be able to shorten your username and the problem won't exist. – meet-bhagdev Jun 24 '15 at 18:02
  • Concerning the driver: As you can tell from the code in my quesiton, I'm trying to use `dblib`. But I'd use whatever works on my setup. ~~ Concerning why I cannot change my login data: As I wrote, I use a Microsoft Access Web App. You don't have your own **Azure** user account in such a case. Instead, Microsoft creates a database on one of their servers for which you can request the creation of **database** user account using Microsoft Access. Microsoft Access will then give you the login data. Therefore, you don't have any control on what the username or password look like. – Hauke P. Jun 24 '15 at 18:34
  • Can you point me to some documentation that shows you how you setup the Microsoft Access Web App. We currently do not support PHP on Linux. We are currently working on a PHP driver for linux but in the meanwhile we might have to find an alternate solutions. Look forward to hearing from you. – meet-bhagdev Jun 25 '15 at 02:59
  • Here's documentation about how to create a Microsoft Access Web App: https://support.office.com/en-us/article/Create-an-Access-app-25f3ab3e-510d-44b0-accf-b976c0813e71. And here's documentation about how to get the login data: http://blogs.technet.com/b/the_microsoft_access_support_team_blog/archive/2014/03/24/how-to-make-external-connections-to-an-access-web-app-new.aspx You might want to take at a bit more than the first minute of the video further down on that page. That explains perfectly how to get your database login information. (And should show why I cannot choose my own login data.) – Hauke P. Jun 25 '15 at 08:16
  • Thanks Hauke, I am looking at it and will keep you posted of my findings. – meet-bhagdev Jun 25 '15 at 17:47
  • Some additional update: I tried ODBC with the [Microsoft ODBC Driver 11 for SQL Server on Linux](https://msdn.microsoft.com/en-us/library/hh568451(v=sql.110).aspx). (I specifically used the Red Hat 6 driver using [this guide](https://code.google.com/p/odbc/wiki/InstallingMicrosoftDriverOnDebianLinux).) Unfortunately this driver seems to be heavily flawed and I directly ran into this and other issues: http://stackoverflow.com/questions/14628409/im-getting-string-data-right-truncation-errors-from-php-using-odbc-and-conne So that driver does not seem to help me either. – Hauke P. Jun 25 '15 at 21:48
  • 1
    Hi Hauke, just wanted to update you that we are in touch with the Access Web apps team and waiting on an update :) – meet-bhagdev Jun 30 '15 at 17:48

2 Answers2

2

"LOGINREC" structure can be a maximum of 30 characters. You'll have to shorten long strings.

Vlastislav Novák
  • 349
  • 4
  • 21
-1

Have you tried SqlSrv - an alternative driver for MS SQL from Microsoft. Per my understanding, PDO_DBLIB - extension: http://php.net/manual/en/ref.pdo-dblib.php is not available anymore on Windows with PHP 5.3 or later. It's recommended to use SqlSrv and I have tested your long dbUser & dbPass, it works fine on my side via using SqlSrv: enter image description here

Please feel free to let me know if I have any misunderstood on your issue.

Edit:

I have tried odbc_connect on my side as well and it works fine enter image description here

For more information about Microsoft SQL Server ODBC driver for Linux, you can refer to http://www.codesynthesis.com/~boris/blog/2011/12/02/microsoft-sql-server-odbc-driver-linux/ & http://www.easysoft.com/developer/languages/php/sql_server_unix_tutorial.html#driver and it is not too difficult to install it on 64-bit Debian or Ubuntu.

Ming Xu - MSFT
  • 2,116
  • 1
  • 11
  • 13
  • While preparing the PHP script on Windows, SqlSrv worked beautifully. However, as I wrote in my question, the target system is Debian 7.7 x64. So I can't use the Windows-only SqlSrv driver. – Hauke P. Jun 23 '15 at 19:11
  • Sorry that I missed OS info, I have tried odbc_connect on my side as well and it works fine, I will edit my reply since there has characters # limit in comment. – Ming Xu - MSFT Jun 24 '15 at 05:17
  • As I've written in the latest comment on the question, the currently available Microsoft SQL Server driver version for Linux seems to be somewhat broken. So that won't help me either. (I'll try the FreeTDS implementation at some point though. Maybe that helps.) – Hauke P. Jun 25 '15 at 21:51
  • If ODBC driver doesn’t work, then FreeTDS would be the only way we could think of. I will also work internally to see if there is a way for decreasing username/password characters to < 30. – Ming Xu - MSFT Jun 28 '15 at 15:15
  • Hi Hauke, as we have worked internally to see if there is a way for resolving this issue, in order to open an collaboration request with development and get a formal consideration, could you please open a support ticket to Microsoft Access Web App team at:support.microsoft.com/oas , feel free to let me know if you have any concerns. – Ming Xu - MSFT Jul 03 '15 at 03:01