question

Stefan-1807 avatar image
0 Votes"
Stefan-1807 asked Stefan-1807 commented

Best provider to use in SSAS for Data Source Connectionstring to SQL Server 2019

Hi,
I´ve developed a SSAS 2019 Tabular cube whos data source is a SQL Server 2019 database.
In previous versions I have used the SQL Native client as provider when connecting to data source. Like this:
Provider=SQLNCLI11;Data Source=my_server;Initial Catalog=my_databasename;Integrated Security=SSPI;Persist Security Info=false

However I found the information below on the following link:
https://docs.microsoft.com/en-us/sql/connect/oledb/oledb-driver-for-sql-server?view=sql-server-ver15

"Starting in SQL Server 2005 (9.x), the SQL Server Native Client (SNAC) includes an OLE DB provider interface (SQLNCLI) and is the OLE DB provider that shipped with SQL Server 2005 (9.x) through SQL Server 2012 (11.x). It was announced as deprecated in 2011 and it is not recommended to use this driver for new development.
To use the new Microsoft OLE DB Driver for SQL Server in existing applications, you should plan to convert your connection strings from SQLOLEDB or SQLNCLI, to MSOLEDBSQL."

And here (https://docs.microsoft.com/sv-se/sql/connect/connect-history?view=sql-server-ver15) you can find this:
"There are three distinct generations of Microsoft OLE DB providers for SQL Server ... The SQL Server Native Client includes an OLE DB provider interface (SQLNCLI) and is the OLE DB provider that shipped with SQL Server 2005 through SQL Server 2017. It was announced as deprecated in 2011 and it isn't recommended to use this driver for new development. In 2017, OLE DB data access technology was later undeprecated and a new planned release was announced for 2018. The new OLE DB provider is called the "Microsoft OLE DB Driver for SQL Server" (MSOLEDBSQL) and is currently maintained and supported."

Does this mean that it is better to use the "Microsoft OLE DB Driver for SQL Server" instead of "SQL Server Native Client" that I did before. So that the connectionstring should look something like this instead?:
Provider=MSOLEDBSQL.1;Data Source=my_server;Integrated Security=SSPI;Initial Catalog=my_databasename

Best regards
Stefan

sql-server-generalsql-server-analysis-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered Stefan-1807 commented

Yes, that is what it means.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Sorry for late reply. Thanks a lot Erland.

0 Votes 0 ·
LukasYu-msft avatar image
0 Votes"
LukasYu-msft answered LukasYu-msft edited

I think the doc clarify the issue. Do you have further question or what particular question bothers you now ? We would be happy to help further.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Do you have other questions? Could we assist you further?

0 Votes 0 ·