System DSN (ODBC) configuration using 2003 GPO

Posted on 2011-10-11
Last Modified: 2012-05-12
I need to configure System DSN setings (point to SQL DB) in all windows 7 systems using windows 2003 GPO. Can anyone help.
Question by:sumod_jacob
    LVL 5

    Expert Comment

    by:Prashant Shrivastava

    Author Comment

    Understand this solution can't provide SQL authentication, will this required to go around the system to enter the password in system DSN?.
    LVL 5

    Accepted Solution

    In that case you need to use the script and run it globally.

    link is here - you may need to modify this a bit.

    '## Title: DSN.vbs
    '## Author: Eric Payne  
    '## Date: 2/25/2007
    '## Purpose:  
    '##         For use in scripts that require SQL System DSNs (setup and pre-req check)
    '##         1. Checks if SQL System DSN exists if not, exits
    '##         2. Creates SQL System DSN by running /Setup and prompt user for values (or uses defaults)
    '## Requirements:
    '##         1. Must have administrative permissions to create SQL system DSN.
    '##         2. Change Const DSN: DSN="Your DSN Name"
    '## Basic Logic:  
    '##         1. Checks to see if your SQL System DSN exists
    '##         2. If you run the script with /Setup, script will prompt you for values and creates SQL system DSN
    On Error Resume Next
    Dim objArgs: Set objArgs = WScript.Arguments
    if objArgs.Named.Exists("Setup") Then Setup()
    'Pre-req check
    If not DSNExists(DSN) Then
          WScript.Echo vbnewline & "***Failed Pre-Req. A SQL system DSN must first exist to MY_DATABASE, " & _
              "use /Setup to create one***"
    End if
    'Do database stuff here
    '     Dim objADOConnection: Set objADOConnection = CreateObject("ADODB.Connection")
    '     Dim objADORS: Set objADORS = CreateObject("ADODB.RecordSet")
    '     Dim strTable: strTable = "MY_TABLE"
    '     objADOConnection.Open DSN
    '     if err <> 0 then  
    '           WSCript.Echo "Problem Opening: " & DSN
    '           WScript.Quit
    '     End if
    Sub Setup()
          'DESC: Creats a System DSN to database
          'Ask user for values (or use defaults)
          Dim strDataSourceName: strDataSourceName = InputBox("Enter Data Source Name","Data Source Name","MY_DATABASE_DSN")
          Dim strSQLServer: strSQLServer = InputBox("Enter SQL Server Name","SQL Server","SQL001")
          Dim strDescription: strDescription = InputBox("Enter Description","Description","DSN to MY DATABASE")
          Dim strDataBaseName: strDataBaseName = InputBox("Enter Database Name","Database","MY_DATABASE")
          'Set Values
          Const SystemFolder = 1  
          Dim objFSO: Set objFSO = CreateObject("Scripting.FileSystemObject")
          Dim strSysPath: strSysPath = objFSO.GetSpecialFolder(SystemFolder)
          Dim strDriverName: strDriverName = "SQL Server"
          Dim strDriverPath: strDriverPath = strSysPath & "\sqlsrv32.dll"
          Dim strTrustedConnection: strTrustedConnection = "Yes"      
          Dim strRegPath: strRegPath = "HKLM\SOFTWARE\ODBC\ODBC.INI\" & strDataSourceName & "\"
          Dim objWshNetwork: Set objWshNetwork = CreateObject ("WScript.Network")
          Dim strLastUser: strLastUser = objWshNetwork.UserName
          Dim objWshShell: Set objWshShell = CreateObject("Wscript.Shell")
          'Create Key
          objWshShell.RegWrite strRegPath,""
          'Create Values
          objWshShell.RegWrite strRegPath & "Database",strDatabaseName
          objWshShell.RegWrite strRegPath & "Description",strDescription
          objWshShell.RegWrite strRegPath & "Driver",strDriverName
          objWshShell.RegWrite strRegPath & "LastUser",strLastUser
          objWshShell.RegWrite strRegPath & "Server",strSQLServer  
          objWshShell.RegWrite strRegPath & "Trusted_Connection",strTrustedConnection
          objWshShell.RegWrite "HKLM\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources\" & strDataSourceName,strDriverName
          WScript.Echo "SQL System DSN: " & strDataSourceName & " has been created"
          Set objFSO = Nothing
          Set objWshNetwork = Nothing
          Set objWshShell = Nothing
    End Sub
    Function DSNExists(strValueName)
          Dim strComputer: strComputer = "."
          Dim objReg: Set objReg = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & strComputer & _
          Dim strKeyPath: strKeyPath = "SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources"
          Const HKEY_LOCAL_MACHINE = &H80000002
          Dim strDWValue
          objReg.GetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strDWValue
          if strDWValue <> "" Then
                DSNExists = vbTrue
                DSNExists = VBFalse
          End if
    End Function  

    Author Comment

    Pls..can anybody respond...

    Author Comment

    Issue i see is to enter administrator passord to add system DSN entry.

    i am looking for some  unattented installation, i have task to instll iMiS software to install in all windows 7 user machines,  it required to add System DSN entry for connecting to SQL servers. Is there any other way like custom  GPO templates or third party softwares to do this work unattended?..

    Author Comment

    Finaly i have created User DSN to solve the issue. System DSN required Administrator rights to configure.

    Author Closing Comment

    Not got the proper answer for my question

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now