Announcement

Collapse
No announcement yet.

ODBC System DSN Creation - script

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • ODBC System DSN Creation - script

    Can any 1 post a working VBScript or another script to automate the ODBC source creation so i can use that in GPO for the domain ?
    I found some scripts on the net but they did not work.

    Thanks.
    Thanks & Regards

    Retaliator

    MCSA/MCSE/CCNA
    Computer Science Graduate

  • #2
    Re: ODBC System DSN Creation - script

    Share your expirence,
    Did you tried the script below as a administrator on a test computer?
    And what happend?

    At any rate, here’s a script that creates a DSN that uses SQL Server: ('Hey, Scripting Guy!' q&a Nov04)
    Code:
     
    Const HKEY_LOCAL_MACHINE = &H80000002
    Const HKEY_CURRENT_USER 	= &H80000001
    
    strComputer = "." 
     
    Set objReg=GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & _ 
        strComputer & "\root\default:StdRegProv")
     
    strKeyPath = "SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources"
    strValueName = "Script Repository"
    strValue = "SQL Server"
    objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue
     
    strKeyPath = "SOFTWARE\ODBC\ODBC.INI\Script Repository"
    
    objReg.CreateKey HKEY_LOCAL_MACHINE,strKeyPath
    
    strKeyPath = "SOFTWARE\ODBC\ODBC.INI\Script Repository"
    
    strValueName = "Database"
    strValue = "Script Center"
    objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue
     
    strValueName = "Driver"
    strValue = "C:\WINDOWS\System32\SQLSRV32.dll"
    objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue
    
    strValueName = "Server"
    strValue = "atl-sql-01"
    objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue
    
    strValueName = "Trusted_Connection"
    strValue = "Yes"
    objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue
    The script above creates a DSN that uses SQL Server. Is that important? Yes. You can create DSNs that use other ODBC databases (Access, Oracle, FoxPro, whatever). However, the registry keys and values used when creating a DSN to access an Oracle database aren’t necessarily the same as those used to access a SQL Server database. If you’re using SQL Server, you can just copy the script and change the names of the database, the server, etc. If you’re using a different database, we’d recommend that you manually create a DSN. After you do that, fire up your favorite registry editor and look for the DSN here:
    <hive>\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources
    And here:
    <hive>\SOFTWARE\ODBC\ODBC.INI\Name You Gave the DSN
    Take a look at the registry keys and values required for that type of database, and then plug that information into the above code.



    In the ODBC Manager, you can set up and configure the following three types of DSNs:
    • User DSN
    • System DSN
    • File DSN

    User DSN
    The User DSN is a data source that is user-specific. A User DSN is stored locally but is available only to the user who creates it. User DSNs are not used by Microsoft Query. If you use Microsoft Jet, ODBC, or Structured Query Language (SQL) commands and bypass Microsoft Query, User DSNs are required. User DSNs are stored in the Windows registry under the following key:
    Code:
    HKEY_CURRENT_USER\Software\Odbc\Odbc.ini\Odbc Data sources

    System DSN
    Unlike a User DSN, a System DSN is not user-specific. A System DSN is stored locally and is not dedicated to a particular user. Any user who logs on to a computer that has permission to access the data source can use a System DSN. Some programs, such as Microsoft SQL Server or Microsoft Internet Information Server (IIS), require a System DSN. This DSN must be created on the server where the program is located. System DSNs are stored in the Windows registry under the following key:
    Code:
    HKEY_LOCAL_MACHINE\Software\Odbc\Odbc.ini\Odbc Data sources

    File DSN
    The File DSN is created locally and can be shared with other users. The File DSN is file-based, which means that the .dsn file contains all the information required to connect to the data source. Note that you must install the ODBC driver locally to use a File DSN. Microsoft Query uses File DSNs, but Microsoft Jet and ODBC do not use File DSNs.

    The File DSNs are stored by default in the Program Files\Common Files\Odbc\Data Sources folder. File DSNs are not stored in the Windows registry. The .dsn file is a text file that you can view in any text editor, such as Microsoft Notepad.

    NOTE: When you connect to an existing data source using Microsoft Query, only the available File DSNs that are stored on that computer are displayed. Microsoft Query does not display User or System DSNs. However, you can create a File DSN that points to a System DSN.



    \Rems
    Last edited by Rems; 28th March 2009, 17:39.

    This posting is provided "AS IS" with no warranties, and confers no rights.

    __________________

    ** Remember to give credit where credit's due **
    and leave Reputation Points for meaningful posts

    Comment


    • #3
      Re: ODBC System DSN Creation - script

      Nice script Rem
      Marcel
      Technical Consultant
      Netherlands
      http://www.phetios.com
      http://blog.nessus.nl

      MCITP(EA, SA), MCSA/E 2003:Security, CCNA, SNAF, DCUCI, CCSA/E/E+ (R60), VCP4/5, NCDA, NCIE - SAN, NCIE - BR, EMCPE
      "No matter how secure, there is always the human factor."

      "Enjoy life today, tomorrow may never come."
      "If you're going through hell, keep going. ~Winston Churchill"

      Comment

      Working...
      X