[The first article to be moved from the old site … hooray for progress :-)]

While I won’t ever be as organised as Howard, with his pantechnicon of useful articles, I did get my act in gear to write down the in’s and out’s of linking SQL Server to Oracle, and vice versa. In this first half of the topic, let’s look at how to create a linked server from SQL Server to Oracle – essentially putting SQL Server in the driving seat for any heterogeneous work you might like to do.

Step 1. You will need to install the Oracle client on your SQL Server host. Just so you’re completely clear … YOU WILL NEED TO INSTALL THE ORACLE CLIENT. We’re clear on that now, right? :-). The reason you need this is the same reason you need MDAC on a machine in order to access SQL Server. The client is what provides the network tranport interfaces that lets you talk Oracle’s transport protocol, Tranparent Network Substrate, or TNS. These are bundled up into a package that was historically called SQL*Net, and now is just known as Oracle Net. This is bundled in with the Oracle client, along with useful utilities like a query tool (SQL*Plus), network testing tool (tnsping), configuration wizards (such as Oracle Net Configuration Assistant) and other goodies.

To be completely honest, you can try to create a linked server using Oracle’s instant client, and even try using one of the “clientless” wire protocol drivers offered by third-parties. Best of luck with that :-P.

Step 2. Use the Oracle Net Configuration Assistant to configure tnsnames.ora properly. This is the file used by the Oracle client to find Oracle servers … in much the same way your operating system uses the hosts file to find hosts. The wizard will walk you through a set of dialogs and let you test your config at the end. If you can’t see the icon in the Configuration Tools start folder, you can simply type “netca” at the command prompt to launch the wizard. If that doesn’t work, then you can edit the tnsnames.ora file manually. The format is, in its simplest form:

MyServiceAlias =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = your-host-name-or-ip)(PORT = listener-port-usually-1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = oracle-service-name)
)
)

Fill in the appropriate values for you host, listener port, service name and chosen local alias. Note that an Oracle service name is a published name chosen for the instance … don’t confuse it with the name you see in the windows Services.msc MMC applet if your Oracle instance happens to run on Windows :-). Be very careful not to mess up the formatting of other entries … dangling opening/closing parentheses will put a spanner in the works. The wizard is usually the best bet, as it ensures the formatting is correct. The listening port is usually 1521, the default, in much the same way that 1433 tcp / 1434 udp are SQL Server’s default listening ports.

Step 3. Before trying to configure the link in SQL Server, make certain you can talk to the listener and connect to the Oracle instance from the SQL Server box without any SQL Server involvement.

E.g:

c:> tnsping MyServiceAlias

[ boilerplate stuff … blah blah blah ]

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521)))(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = MyServiceAlias)))
OK (0 msec)

^^^ that’s the thing you want to see … “OK”

c:> sqlplus myusername/mypassword@MyServiceAlias

[ boilerplate stuff … blah blah blah ]

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options

SQL> _

^^^ that’s the magic phrase and prompt … “Connected to:” and the SQL> prompt. type exit or quit to close.

Step 4. (Optional). You might like to create a dedicated Oracle user with which to use the soon-to-be-created link from SQL Server. Something simple will do for this test, but you can go to town if you like. Of course, an existing user could always be used.

SQL> create user oratest identified by oratest
2 default tablespace users
3 quota unlimited on users;

User created.

SQL> grant create session, resource to oratest;

Grant succeeded.

Step 5. Now you’re ready to create your link from SQL Server to Oracle. From within Query Analyzer, Isql, Osql or your favourite query tool, you can issue the following commands

EXEC sp_addlinkedserver @server = ‘TestOraLink’, @srvproduct = ‘Oracle’, @provider = ‘MSDAORA’, @datasrc = ‘MyServiceAlias’

EXEC sp_addlinkedsrvlogin ‘TestOraLink’, false, ‘sstest’, ‘oratest’, ‘oratest’

That creates a logical link called TestOraLink, mapped via the Microsoft OLEDB provider for Oracle to your Oracle service. We then associate a local SQL Server login – sstest – with an equivalent Oracle username and password, in this case oratest.

Step 6. Time to test your link.

SELECT * FROM OPENQUERY(TestOraLink, ‘select * from dual’)

D

X

Fantastic results! Well, OK, you’re just seeing the contents of the ‘dual’ view, but that’s come all the way from your Oracle instance, via your linked server definition.

So now you’re in business, you can go wild.

In the next installment, I’ll show off the three different ways to use your new link … the OPENQUERY, OPENROWSET and four-part-naming techniques. I’ll also highlight some of the flaws in SQL Server’s linked server logic, including the madness of “assumed” and “ignored” case-sensitivity in situations that should be just left in peace!

Happy linking.

Advertisements

What do government systems and databases have in common? The concept of the separation of powers. In a Westminster-style democracy, the three arms of power – the executive, the legislature, and the judiciary – each have separate well defined powers that are forbidden to the other arms of government. For example (and something the crowd at Microsoft should remember), the power to determine punishments is exclusively the right of the judiciary … not criminally convicted monopolists who engage in “self help” :-). But I digress.

The same idea holds in database land. Separate powers and privileges out into discrete realms, and grant those well-defined powers to appropriate users. Works well for DB2, Oracle, SQL Server … but crashes into the safety barrier of poor design when we get to MySQL.

MySQL gets it right in the more-recently added powers, such as creating and executing routines, but its flakey bedrock is shown in the way basic object creation privileges are managed. Creating and dropping tables and databases are controlled by the same two privileges. Read that again – the create privilege spans tables and databases, and the drop privilege is equally-poorly scoped. What it means in practice is that developers who should be given a restricted authority to create objects can now create entire databases, and worse, the power to drop a table comes bundled with the “oops, was that the dev or production database I just wiped” ease of use feature. These should be separate powers … so let’s see how many other people have requested this in the past on the MySQL forums/bug site, and if in the unlikely event that count is zero, I’ll submit it and we’ll see what happens. Stay tuned to watch the fun.