Set DSN encoding for ODBC Driver 11 for SQL Server on Windows 10

Question

TL;TR

How force this ODBC driver to encode in UTF-8?

Detailed description

I'm writing PHP 5. 5. application which connects to database Microsoft SQL Server 2014 via PDO using ODBC driver. More specifically I'm using "ODBC Driver 11 for SQL Server".

I run application in two environments (platforms):

1. Production - Linux/Ubuntu/Appache PHP/UnixODBC
2. Development - Windows 10/Wamp

To configure PDO connection, I'm using DSN sources. To connect to SQL server I use command below:

$conn = new PDO("odbc:mssql_test", $user_name, $password);

Since it's PHP application, I require data from SQL to be returned in UTF-8 encoding. Data in database have czech characters, which is tricky, when data are not encoded in UTF-8.

On production platform, everything works fine. Queries are return as expected (in UTF-8).

However, I was not able configure DSN on Windows 10 (development platform), to get data in UFT-8 (it's seem that I'm getting data encoded in win1250) which is notable when Czech characters occurs as question mark in the result of query.

To configure DSN I'm using Windows DSN management tool located at:

C:\Windows\System32\odbcad32.exe

I added DSN to tab System DSN, but there is no option to set encoding.

Is there any way or work around to get data in UTF-8 on Windows 10 (without using PHP way such as iconv, mb_convert_encoding, since its only development issue resp. DSN configuration issue).

Please note that I've tried thinks like, without success:

$conn = new PDO("odbc:mssql_test", $user_name, $password, array("charset" => "UTF-8"));
$conn->exec("set names utf8");

or (code below works, but UTF-8 is not obtained on Windows)

$dsn = "odbc:DRIVER={ODBC Driver 11 for SQL Server};SERVER=$server_address};CHARSET=UFT-8";
$conn = new PDO($dsn, $user_name, $password);

Any help would be really appreciated!

EDIT after 3 months I end up with switching into FreeTDS and Linux for development as well as production environment. For the time being its seem to impossible to configure ODBC driver 11 to encode in UTF-8 while using PHP 5.5. on Windows 10...

Answer 1

When using ODBC on Windows, the database client charset is defined in the language settings for non-Unicode applications. The current ANSI code page (ACP) is used by the SQL Server client.

On Windows 10, click Start and type Region, select Region & language settings and click Administrative language settings. Click Change system locale and check Use unicode UTF-8 ...

That should do the trick.