How to query accross servers using MS SQL Server.

Sometimes you need to query across servers, even servers of different type such as doing a table join from MySQL to Oracle. I’m going to show you two ways you could do this if you have access to an SQL Server. There are ways to do this with other servers, and there are more than two ways to do this in SQL Server, but I’m only covering two methods both using SQL Server here.

Using linked servers:

Execute the following two stored procedures (you will need SA privileges):

exec sp_addlinkedserver @server='server.asdf.com';
exec sp_addlinkedsrvlogin @rmtsrvname='server.asdf.com',@useself=false, @rmtuser='yourusername', @rmtpassword='yourpassword';

Test the connection:

select top 1 * from [server.asdf.com].somedatabase.dbo.sometable;

Using OpenDataSource:

No setup is needed for this method, you just need to alter the FROM clause of your SQL statement:

select * from OpenDataSource('SQLOLEDB','Data Source=server.asdf.com;User ID=yourusername;Password=yourpassword').somedatabase.dbo.sometable;

Comments

There are a few notable differences between the two methods:

  • Using OpenDataSource does not require any extra privileges beyond execute query, where Linked Servers requires admin privileges.
  • Using OpenDataSource requires that the username and password be accessible in some way to your code. Linked Servers hides the login credentials, but will allow anyone with SELECT privileges to the server to authenticate and run queries on any linked servers as the user specified when sp_addlinkedsrvlogin was ran. It’s up to you to determine which is more secure for your environment.

Note, the examples I’ve provided use SQL Server as the linked server, but it’s possible to use any database which you have an ODBC driver for. The Microsoft documentation for each method will help you figure out exactly how if you can’t figure it out.

MD5 in one line of C# code.

Considering the popularity of using MD5 to hash passwords in a database, I’m litterally baffled as to why Microsoft didn’t include a System.Security.Cryptography.MD5() function. This isn’t quite as nice as that would be, but it gets the job done:

string hash = Convert.ToBase64String(new System.Security.Cryptography.MD5CryptoServiceProvider().
   ComputeHash(System.Text.Encoding.Default.GetBytes(SomeString)));

Pretty much every other example I’ve seen are at least 10 lines. The only difference here is that this one returns a string in base64, where most others return a string of hex digits concatenated togeter. That shouldn’t matter if all you’re doing is hashing passwords, but probably won’t do if you plan to share the hash with someone else for varification that a file matches a certain signature.

I’ve created a Visual Studio 2005 snippet file which you can import, then just type md5{TAB}{TAB} to have the code appear.

If you’re concerned about the status of MD5 and it’s use of a hash function, read How to break MD5 and Other Hash Functions [PDF] by Xiaoyun Wang and Hongbo Yu. Realize that the attack they describe is only theoretical, and can’t be used to derrive a password from it’s MD5 hash, nor can it be used to create another password which matches a given hash. So MD5 is still as secure as ever for use as a password hash function.