When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
ASPFAQs.com
Message Board
Related Web Technologies
User Tips!
Coding Tips

Sections:
Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Security
Stump the SQL Guru!
XML Info
Information:
Feedback
Author an Article
ASP ASP.NET ASP FAQs Message Board Feedback
Print this page.
Published: Thursday, October 14, 1999

Accessing the System Registry using MS SQL Server - PART 1

By Julian


Can you read the system registry using MS SQL Server? Of course you can! There's a little known xp procedure in the master database called xp_regread. This stored procedure accepts three parameters. The first one being the root key, next is the path to the key, and finally the key value you are looking to return.

- continued -

Here is a simple example that retrieves the SQL setup location.

USE Master
EXEC xp_regread 'HKEY_LOCAL_MACHINE',
                'SOFTWARE\Microsoft\MSSQLServer\Setup',
                'SQLPath'

This returns 1 row as follows:

Value             Data
----------------- ---------------
SQLPath           D:\MSSQL7

The two field items are Value and Data, where Value is the Registry Key and Data is the value of the Registry Key. What's so great about this stored proc is it has multitude of uses. You could run xp_regread and put the results in an ASP page returning just about any registry value you like. For example, the following code will read the path SQL Server is installed in:

<% Option Explict %>
<% Dim objConn Set objConn = Server.CreateObject("ADODB.Connection") objConn.ConnectionString = "DSN=pubs;UID=sa;PWD=;" objConn.Open 'Must use the master database, where xp_regread exists... objConn.Execute "USE MASTER" Dim strSQL strSQL = "xp_regread 'HKEY_LOCAL_MACHINE'," & _ "'SOFTWARE\Microsoft\MSSQLServer\Setup'," & _ "'SQLPath'" Dim objRS Set objRS = objConn.Execute(strSQL) Response.Write "SQL Server installed at " & objRS("Data") objRS.Close Set objRS = Nothing objConn.Close Set objConn = Nothing %>

Running the xp_regread stored proc will return ANY VALUE from the registry provided that the user running xp_regread has access to that registry key and the master database. Be forewarned, allowing access to this feature has it's own risks. You may not want to allow a user to read your system registry settings. Alot of sensitive data is located in the system registry (i.e. SAM database). Let me know what else you come up with. Have fun!

  • By Julian

    Note: The xp_regread stored procedure has only been tested with SQL 6.5 and SQL 7.0.


    Attachments:

  • Download the source for the xp_regreadexample in text format

  • ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article