How to connect PDO with named Sqlserver instances?

Asked

Viewed 362 times

0

I can’t connect PHP 7.0.36, sqlsrv PDO drive on Ubuntu 16.04 on SQL Server 2012 when the database has a appointed body.

When do I use the standard instance works.

$conn = new PDO('sqlsrv:Server=SERVERNAME;Database=MEUBANCO', 'MEUUSUARIO', 'MINHASENHA');

But when I use the appointed body 

$conn = new PDO('sqlsrv:Server=SERVERNAME\INSTANCENAME;Database=MEUBANCO', 'MEUUSUARIO', 'MINHASENHA');

Error appears:

PHP Fatal error: Uncaught Pdoexception: SQLSTATE[HYT00]: [unixODBC][Microsoft][ODBC Driver 17 for SQL Server]Login timeout

Note: This problem does not happen in Windows 7 + PHP 7.0 or PHP 7.1

php sql-server pdo php-7

  • But you set (fixed) the door too? Because each instance named runs over its own door (I think), just the way you did I think it will try the standard door, which is probably not available, because the instance is using another.

2 answers

1


I believe that the named instances surround each one on its own port, if I am not mistaken the door of the instances change every time it starts, then the way is to fix the port, I believe this is it (if the database server is Windows):

sql server configuration manager

In TCP Port put the door that will be "fixed", a number that is not being used by another service, and then in the PDO:

$conn = new PDO('sqlsrv:Server=SERVERNAME,numero_da_porta;Database=MEUBANCO', 'MEUUSUARIO', 'MINHASENHA');

In place of numero_da_porta the same number you used in the configuration.

If Sqlserver is on linux I think you have to edit the freetds.conf to fix the door, but I really don’t understand much of it so I can’t say, if I’ve been wrong on anything you can let me know/comment below.

  • Thank you I will check with the database team the possibility to fix the port number.

  • It seems that this problem is relatively common I found two on the link on Github about this problem the first link has the list of similar problems. The second link has a possible outline solution

  • 1

    William you’re right one solution is to fix the door. Another solution is to detect which port, see link https://github.com/microsoft/msphpsql/issues/927#issuecomment-510185524

0

If I understand correctly, you have a PHP Linux client to access SQL Server.

So you need to install Freetds on Linux [http://www.freetds.org/] .

My Freetds configuration file is on /usr/local/etc/freetds/freetds.conf :

[mosaico]
    host = servidor.meu.dominio
; ou host = 192.168.1.2  se preferir usar o IP do servidor ao invés do nome
    port = 1433
    tds version = 7.4
    client charset = UTF-8

The entry named as mosaico should be used for the bank call by PDO. Since PDO knows that the driver is Sqlserver, Freetds will provide additional information. But user and password do not stay in Freetds config file .

I am not accessing with PDO. I am using: [https://www.php.net/manual/en/book.mssql.php]

My config file in the PHP application where I refer to the Freetds configuration:

[ms_sql_server]
host=mosaico
user=MosaicoLeitura
password=MosaicoLeitura

And here as I call the MS SQL Server database:

final class MsSqlSrvDAOFactory extends DAOFactory
{
    private static $host = null;
    private static $user = null;
    private static $pass = null;

    function __construct()
    {
        $config         = Config::get()["ms_sql_server"];
        self::$host     = $config["host"]; //vai retornar mosaico
        self::$user     = $config["user"]; //vai retornar MosaicoLeitura
        self::$pass     = $config["password"];// também retorna MosaicoLeitura
    }

    /**
     *
     * {@inheritDoc}
     *
     * @see \DarthEv\Core\interop\dao\DAOFactory::getUteDAO()
     */
    public function getUteDAO() {
        try {
            return new MsSqlSrvUteDAO(self::$host, self::$user, self::$pass);
        } catch (Exception $e) {
            throw $e;
        }
    }
}

The following prompts the driver (I omitted the rest of the class to show how to instantiate):

$conn           = null;
$conn = odbc_connect(self::$host, self::$user, self::$pass);
//a consulta vai a seguir:
$rs = odbc_exec($sql, $conn);

Above: $host is the name "mosaic" as in the previous class, which reads the application configuration data.

I hope I’ve helped.

Browser other questions tagged php sql-server pdo php-7

You are not signed in. Login or sign up in order to post.