0

I need to configure RDS Farm High Availability based on Windows Server 2016 with MS SQL Server 2016. One of the steps is connect to database. All available guides tell me to use SQL Server Native client (SQLNCLI). But it remains deprecated and it is not recommended to use in production.

https://docs.microsoft.com/en-us/sql/relational-databases/native-client/sql-server-native-client?vie...

They tell to start using Microsoft OLE DB Driver for SQL Server (MSOLEDBSQL).

But there are no guides on how to write connection string using Microsoft OLE DB Driver for SQL Server. It seams even that Microsoft stopped writing guides about on premises environment. Now they touch only Azure.

This was the connections string for SQL Native Client:

DRIVER=SQL Server Native Client 10.0;SERVER=<SQL Server Name>;Trusted_Connection=Yes;APP=Remote Desktop Services Connection Broker;DATABASE=<DB Name>

How to write properly connection string for Microsoft OLE DB Driver for 2016 SQL Server?

Something other that I should know about using and configuring Microsoft OLE DB Driver for SQL Server with RDS High Availability farm?

Link
  • 25
  • 4

2 Answers2

0

I lost also 1 days searching for it. The problem with me was that I used a named instance in my connection string. The broker didn't accepted it. With the native client this wasn't a problem.

We filled in the sql port instead of server\instance and it worked.

The driver version can you find through: odbcad32 -> File DSN -> Add -> select the driver -> advanced

Connection string used:

DRIVER={ODBC Driver 17 for SQL Server};Server=tcp:<sql server>,<server port>;Database=<Database>;APP=<APP Name>;Trusted_Connection=yes;
RuLe
  • 1
  • 1
0

I ended up with this string that is working for me:

DRIVER={ODBC Driver 13 for SQL Server};SERVER=tcp:RDSServer.best-company.local,1433;DATABASE=RDDB;APP=Remote Desktop Services Connection Broker;Trusted_Connection=Yes;

After creating database you MUST assign dbowner permission in SQL Management Studio to RDS servers gorup. It is not done automatically.

ODBC driver version, as RuLe sad you can take from C:\Windows\System32\odbcad32 For SQL Server 2016 it is 13 version.

Need to note that

Trusted_Connection=Yes;

should be written only in this way. If you write YES, instead of Yes - database configuration fails with not very descriptive error in TerminalServices-SessionBroker event log

The object is already in the list.

Also the thing that I noticed is that after some (I think about 5) failed attempts to configure database - you are totally locked with the error in TerminalServices-SessionBroker event log

Account locked due to RDDB bad passwords

I could not find where is this account that is getting locked out and how to unlock it. After about a day it is getting unlocked automatically and you can try again.

The path to database folder is indeed optional and you can leave it empty.

Link
  • 25
  • 4