ODBC System DSN Creation – script

Home Forums Scripting Windows Script Host ODBC System DSN Creation – script

This topic contains 2 replies, has 3 voices, and was last updated by Rems Rems 12 years, 6 months ago.

Viewing 3 posts - 1 through 3 (of 3 total)
  • Author
    Posts
  • Avatar
    Retaliator
    Member
    #120000

    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.

    Rems
    Rems
    Moderator
    #226163

    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 & “rootdefault:StdRegProv”)

    strKeyPath = “SOFTWAREODBCODBC.INIODBC Data Sources”
    strValueName = “Script Repository”
    strValue = “SQL Server”
    objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue

    strKeyPath = “SOFTWAREODBCODBC.INIScript Repository”

    objReg.CreateKey HKEY_LOCAL_MACHINE,strKeyPath

    strKeyPath = “SOFTWAREODBCODBC.INIScript Repository”

    strValueName = “Database”
    strValue = “Script Center”
    objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue

    strValueName = “Driver”
    strValue = “C:WINDOWSSystem32SQLSRV32.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:
    SOFTWAREODBCODBC.INIODBC Data Sources
    And here:
    SOFTWAREODBCODBC.ININame 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:HKEY_CURRENT_USERSoftwareOdbcOdbc.iniOdbc Data sources[/CODE]

    [B]System DSN[/B]
    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_MACHINESoftwareOdbcOdbc.iniOdbc Data sources [/CODE]

    [B]File DSN[/B]
    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 FilesCommon FilesOdbcData 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[CODE]HKEY_CURRENT_USERSoftwareOdbcOdbc.iniOdbc Data sources[/CODE]

    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:HKEY_LOCAL_MACHINESoftwareOdbcOdbc.iniOdbc Data sources [/CODE]

    [B]File DSN[/B]
    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 FilesCommon FilesOdbcData 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[CODE]HKEY_LOCAL_MACHINESoftwareOdbcOdbc.iniOdbc Data sources [/CODE]

    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 FilesCommon FilesOdbcData 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

    Avatar
    Dumber
    Participant
    #196186

    Re: ODBC System DSN Creation – script

    Nice script Rem :)

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic.