Trying to setup a linked server to a SQLite database in a SQL 2012 cluster (active/passive) environment.
I'm able to get the linked server setup and have had SOME success querying, but it is very inconsistent.
For example, when I first setup this linked server, I was able to run the query from the active SQL server (Windows Authentication, logged in as administrator), the passive SQL server (Windows Authentication, logged in as administrator), and Management studio on my computer (Windows Authentication, logged in as myself). I had this setup Monday evening before going home. Next morning, I came in and tried running the query again and could only query from the two SQL servers, but not my desktop (below errors show up).
Configuration:
SQL 2012 Enterprise Cluster (active/passive) with multiple SQL instances installed and running balanced across each of the two servers.
Connecting to Windows Server 2012 with SQLite DB using SQLite ODBC driver 64-bit
In essence, below has been my testing so far.
1) Setup linked server (Server #1 Active, Query from all clients ok [Server #1, Server #2, My Desktop])
2) Next day tested query (Server #1 ok, Server #2 ok, My Desktop NOT OK)
3) Moved active SQL server instance to Server #2 (Server #1 NOT OK, Server #2 ok, My Desktop NOT OK)
4) Moved active SQL server instance back to Server #1 (Server #1 ok, Server #2 ok, My Desktop NOT OK)
Now I have played with other things as well such as:
[ul]
[li]using SQL authentication instead.[/li]
[li]set security to be made using security context "X" with Windows Login[/li]
[li]setup permissions on the SQL Servers c:\users\[SQL Service User]\appdata\local\temp folder to allow read/write to certain groups and users[/li]
[li]looked into dcom settings on SQL Servers[/li]
[li]changed security on MSDAINITIALIZE according to other articles regarding this[/li]
[/ul]
OLE DB provider "MSDASQL" for linked server "DBNAME" returned message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed".
OLE DB provider "MSDASQL" for linked server "DBNAME" returned message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed".
OLE DB provider "MSDASQL" for linked server "DBNAME" returned message "connect failed".
Msg 7303, Level 16, State 1, Procedure ViewName, Line 5
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "DBNAME".
Hoping that someone else can provide some ideas here.
I'm able to get the linked server setup and have had SOME success querying, but it is very inconsistent.
For example, when I first setup this linked server, I was able to run the query from the active SQL server (Windows Authentication, logged in as administrator), the passive SQL server (Windows Authentication, logged in as administrator), and Management studio on my computer (Windows Authentication, logged in as myself). I had this setup Monday evening before going home. Next morning, I came in and tried running the query again and could only query from the two SQL servers, but not my desktop (below errors show up).
Configuration:
SQL 2012 Enterprise Cluster (active/passive) with multiple SQL instances installed and running balanced across each of the two servers.
Connecting to Windows Server 2012 with SQLite DB using SQLite ODBC driver 64-bit
In essence, below has been my testing so far.
1) Setup linked server (Server #1 Active, Query from all clients ok [Server #1, Server #2, My Desktop])
2) Next day tested query (Server #1 ok, Server #2 ok, My Desktop NOT OK)
3) Moved active SQL server instance to Server #2 (Server #1 NOT OK, Server #2 ok, My Desktop NOT OK)
4) Moved active SQL server instance back to Server #1 (Server #1 ok, Server #2 ok, My Desktop NOT OK)
Now I have played with other things as well such as:
[ul]
[li]using SQL authentication instead.[/li]
[li]set security to be made using security context "X" with Windows Login[/li]
[li]setup permissions on the SQL Servers c:\users\[SQL Service User]\appdata\local\temp folder to allow read/write to certain groups and users[/li]
[li]looked into dcom settings on SQL Servers[/li]
[li]changed security on MSDAINITIALIZE according to other articles regarding this[/li]
[/ul]
OLE DB provider "MSDASQL" for linked server "DBNAME" returned message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed".
OLE DB provider "MSDASQL" for linked server "DBNAME" returned message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed".
OLE DB provider "MSDASQL" for linked server "DBNAME" returned message "connect failed".
Msg 7303, Level 16, State 1, Procedure ViewName, Line 5
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "DBNAME".
Hoping that someone else can provide some ideas here.
