Browsing:

Tag: MSSQL

Linked server setup – Back to basics (error 7399)

I recently received this question:

Why am I getting an error 'Invalid authorization specification'. I used my SA account, but i keep getting the error that the credentials specified are incorrect.

There is a very simple answer to this question, which I will share with you.

The error you get when the credentials are invalid:LSSLinked server is a connection between two servers in your network. If you want to connect the servers, the specified credentials need to be known on both servers, So, unless you use the same password for sa acounts on all your network server, which is never recommended in the first place! I advise you to create a specific Linked server sql account, for example I created a LS_2014 account on both servers and specified on which databases the Linked server is allowed to read and select data, as you can see in the example below.

ls_account

ls_acc

This keeps your databases secure and easier to manage.

You could also set it to use your current logged in account, but you need enough permissions on all servers and depending on who is using the SQL server, the linked server might not be available.

If you use the account specifically created for the Linked server and test the linked server account, the connection will be successful and you will see the table you granted read permissions on is available in the linked server dropdown.


Creating a linked server ´MySQL to MSSQL´(query the MySQL database without openquery function)

In addition to my previous linked server tutorials, I decided it is time to add MySQL to the linked server series.
In order to have the bug tracking application, Mantis migrated from linux and have it run on a windows environment, I wanted to create a replication between SQL2008 and MySQL, but then I thought, why not try out a virtual linked server again first, to test Mantis isntallation on a Windows based installation, since the online promise of Mantis on a MSSQl environment is not very promising. So today we will create a linked server from MySQl to MSSQL on a windows 2008R 64 bit environment.

Create DNS for MySQL

In order to do so, We first need to install the correct drivers in order to create a ODBC DSN, Just download the drivers from Mysql developers site http://dev.mysql.com/downloads/connector/odbc/ and install them to your database server. DriversIf we see the listed drivers, it means we can create a new DSN, so open up the System DSN tab and ADD a new DSN, you must fill in the correct credentials, for example:ODBC_Connector

Data Source Name: Enter a describing name, so you can see what it does, you might have more linked servers or other connectors running on the same server.
Description: this isnt maditory, but if you want to be more specific, be my guest.
Server: in my case,it's localhost, as this is a test server and MSSQL and MySQl are on the same server.
Insert username and password, when this is done. The database will display the possible databases you can connect to, in the dropdown.

Click OK and as you can see the System DSN has been added to ODBC.

Create new Linked Server

When this is done, it's time to open up the MSSQl server and add a new linked server to the Server Objects.4_create_LInkedsname your linked server, I give it the same name as the SystemDSN. And choose the correct provider: Microsoft OLE DB Provider for ODBC Drivers. and datasource equels DSN name.
You need to fill in all the credentials for the provider string, for example:

DRIVER=(MySQL ODBC 5.2 ANSI Driver);SERVER=localhost;PORT=3306;DATABASE=mantisbt; USER=user;PASSWORD=password;OPTION=3;

Note: meaning of OPTION=3 in the MySQL connection string:
Option=1 FLAG_FIELD_LENGHT: Do not Optimize Column Width
Option=2 FLAG_FOUND_ROWS: Return matching rows
Option=3 option 1 and 2 together

Now click OK, this is always the most fun part to me! when it says connection tot the linked server succeeded!

In addition to this, you can enable provider options on the SQLOLEDB, In my case I select the Dynamic Parameter and Allow inprocess.

Now, lets run the test and see if it connects with the databases, as you can see, it connects all the databases available on the MySQL server.

Connection test

But, most important, we can query it directly. Wheeee!

Linked server without OpenQuery function (Tip!)

Maybe you have read other MySQL linked server tutorials before this one and found out that you could only query the mysql database using the openquery() function or maybe that IS  the reason it brought you to this site. Extra, as in extra work, is never fun! With the correct ODBC driver and the right provider options, you can query the MySQl database, just like any other MSSQL database on your MSSQL server. Just follow the tutorial above and don't forget to  enable the correct provider options. Cheers!


Troubleshooting a linked server (error 7302 Could not create an instance of OLE DB provider ‘OraOleDB’)

I spent the last two days looking on internet for a solution on a linked server error know as '7302', there are a lot of hits and the same 3 or 4 solutions keep coming up, I tried them all but no success. To save you from going through endless solutions I am going to sum up all the possible solutions, ending with the most valuable solution, well at least it was the best fix for me.

Here are links to linked server articles I wrote before:

- building a linked server
- Creating jobs and queries with linked server

Now, the dreading 7302 error Could not create an instance of OLE DB provider 'OraOleDB'
First a solution that saved our asses last time, but this won't always do the trick! "After installing a new service pack to the mssql server in 2011, the linked server stopped working, giving an 7302 error 'Could not create an instance of OLE DB provider 'MSDAORA'. If your LS stops working after it has been working fine for months and all the tips above are working, i found a quick work around to get your Linked server to work again, just simply check the dynamic parameter box and save and then uncheck it again."

But this time we didn't get away with it this easy, almost the same error, different solution needed

once again this error occurs after a series of windows updates we performed, so we started undoing the updates, but ofcourse the error stayed.

On the internet they advise you to change the DCOM security of the MSDAINITIALIZE (Acces Permissions was on Use default, change it to customize, if not already, change the other 2 in customize as well.)

I added the domain user to the permission section. But this solution didn't work in my case. secondly we opened the Registry and look for: HKEY_LOCAL_MACHINE\SOFTWARE\Classes\AppID\{2206CDB0-19C1-11D1-89E0-00C04FD7A829} and change the owner to administrator, but in my case administrator was already the owner, so no luck here. (source: http://blogs.msdn.com/b/dataaccesstechnologies/archive/2011/09/28/troubleshooting-cannot-create-an-instance-of-ole-db-provider.aspx)

I also checked the Environmental Variables to see if the PATH still contained oracle (e.g. D:/ORACLE/BIN;D:/Oracle/Client;)

Then i tried reconfiguring the Oracle ODAC install in cmd:

D:\oracle>configure
D:\oracle>configure all orahome

but still no luck...

Sometimes restoring your dll files from the day before the error occurs does the trick, but again..nothing.

So, my final solution, re-install the oracle client, this time, with a standard oracle tree that contains some tools to check network and database connection, tnsping and sqlplus.

(re)installing oracle environment:

i used two oracle installations

- Oracle Database 11g Release 2 Client (11.2.0.1.0) for Microsoft Windows x64 (64-bit) this installs the standard oracle home and tools like tnsping and sqlplus

- 64-bit ODAC 11.2 Release 5 (11.2.0.3.20) Xcopy for Windows x64
for a non heavy client for LS to depent on.

first install the oracle 11.2.0.1.0 client and tools.

edit the tns names ora file to your database:

# tnsnames.ora Network Configuration File: D:\oracle\product\10.2.0\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
Databasename =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = fillinurhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = Databasename)

Secondly we reinstalled the ODAC again, now the OraOleDB drivers are available again. Everything is configured for the LS again. MSSQL needs to reload the OraOLEDB file in, for it will keep the old, corrupt file in use, we need to restart the mssql servers.

So if you walked the same path like me, just reinstall oraclient environment. Good luck!

Special thanks to B. Kooiman for helping with the oracle install!


Creating a linked server Oracle to MsSQL and common Error 7302 Could not create an instance of OLE DB provider ‘MSDAORA’

In some cases it can be handy if you can query a database from one database server to the other and copy data and tables.

Now the fun part is, we can use this on different databases as well, like Oracle, MySql e.g. how cool is that!
Let's set up a linked server to Oracle.
If you don't have oracle available, you can download and install the Oracle Express edition, which we can get for free from the Oracle site.

After you have succesfully installed Oracle server, fire up MSSQL.

First we are going to create a new database for this Linked Server Project, let's call it the 'FunFactory' a great program from the '80.

Insert some tables and rows and we're done! Now we open the Server Objects in the Object Explorer and right click 'add new linked server':

Now it's very important to fill in the right credentials: You need to connect Oracle on it's TNS name, for example, Oracle Express the default is XE, now press 'script from', to see the script that will execute when you press ok.

Ok, enought with the boring GUI, we are going to create another linked server, but this time we script it:

DECLARE @vcLinkedServer VARCHAR(255)
DECLARE @vcLocalUser VARCHAR(255)
DECLARE @vcRemoteUser VARCHAR(255)
DECLARE @vcRemotePass VARCHAR(255)
DECLARE @vcRemoteTNS VARCHAR(255)
DECLARE @ncStatement NVARCHAR(MAX)
SET @vcLinkedServer = 'FunFactory'
SET @vcLocalUser = SYSTEM_USER
SET @vcRemoteTNS ='XE'
SET @vcRemoteUser = 'Fun'
SET @vcRemotePass = 'Factory'
/*
SET @ncStatement = 'USE MASTER
GRANT ALTER ANY LINKED SERVER TO ' + SYSTEM_USER + '
USE ' + DB_NAME()
EXEC sp_ExecuteSQL @ncStatement
*/
BEGIN TRY
EXEC sp_DropServer @vcLinkedServer, 'droplogins'
END TRY
BEGIN CATCH
END CATCH
EXEC sp_AddLinkedServer @vcLinkedServer, 'Oracle', 'OraOLEDB.Oracle', @vcRemoteTNS
EXEC sp_AddLinkedSrvLogin @vcLinkedServer, 'FALSE', @vcLocalUser, @vcRemoteUser, @vcRemotePass
DECLARE @vcTableName VARCHAR(255)
DECLARE @vcFieldName VARCHAR(255)
DECLARE @vcFieldType VARCHAR(255)
DECLARE @vcColumnsDest VARCHAR(MAX)
DECLARE @vcColumnsSrc VARCHAR(MAX)
DECLARE @iIsNullable INT

That's better! To check if both linked servers excist, we do a spfile check:

exec sp_helpserver

Great, this part seems to work!

Now we need to make sure we can establish a connection to Oracle
make sure Oracle is installed properly or it will result in some common errors:

Error 7302: Could not create an instance of OLE DB provider 'MSDAORA'

There is 2 things you need to do here:

Check if provider oraOLEDB is available

And make sure ‘Allow In process’is checked in the provider options

 

If it still does not work:
Make sure correct OLEDB (and oracle client) are installed. If not, search for ODAC on the Oracle site.

http://www.oracle.com/technetwork/database/windows/downloads/index-090165.html

In our case SQL is on a 64 bit server, so we choose the ODAC x64 zip file
In order to get it installed, we need an ‘oracle way’ just running the install.bat won’t do the trick, or oracle wouldn’t be oracle 😉
We run the install from cmd or Powershell:

C:\Teletraan_I\Mellie>D:
D:\>cd ODAC112021_x64
D:\ODAC112021_x64>install
D:\ODAC112021_x64>install all d:\oracle orahome
D:\ODAC112021_x64>cd..
D:\>cd oracle
D:\oracle>configure
D:\oracle>configure all orahome

On last thing could stand in your way, a classic oraclient failure:
The path is not inserted in the environmental settings:
Go the PATH and add the location of the newly installed oracle home.

PATH:D:\oracle\bin;D:\oracle;

Now the Oracle dir is created on your computer and the linked server is airing, Hooray! It kinda feels like swearing in the Church, installing an oracle client on your SQL server, but what needs to be done….

You can now write queries to copy oracle tables over to your SQL server and what ever you want to do with data in a database!!! I absolutely love this Linked server thingy!

In my next Linked Server post, I will show you some cool stuff that you can do with data in Linked servers! note: here is the link: Creating jobs and queries with linked server

Note:
After installing a new service pack to the mssql server, the linked server stopped working, giving an 7302 error 'Could not create an instance of OLE DB provider 'MSDAORA'. If your LS stops working after it has been working fine for months and all the tips above are working, i found a quick work around to get your Linked server to work again, just simply check the dynamic parameter box and save and then uncheck it again. Weird but it does work!

If this quick fix does not do the trick, I wrote a more detailed troubleshoot on linked server here: Troubleshoot linked server