?
Solved

System DSN (ODBC) configuration using 2003 GPO

Posted on 2011-10-11
7
Medium Priority
?
677 Views
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.
0
Comment
Question by:sumod_jacob
  • 5
  • 2
7 Comments
 

Author Comment

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

Accepted Solution

by:
Prashant Shrivastava earned 375 total points
ID: 36951089
In that case you need to use the script and run it globally.

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

http://gallery.technet.microsoft.com/scriptcenter/635ff2a1-4188-4664-8cd8-4bd3bf832592

'#####================================================================================  
'## 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
 
'Arguments
Dim objArgs: Set objArgs = WScript.Arguments
if objArgs.Named.Exists("Setup") Then Setup()
 
'Pre-req check
Const DSN = "MY_DATABASE_DSN"
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***"
      WScript.Quit
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
      Wscript.Quit
End Sub
 
Function DSNExists(strValueName)
      Dim strComputer: strComputer = "."
      Dim objReg: Set objReg = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & strComputer & _
          "\root\default:StdRegProv")
      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
      Else  
            DSNExists = VBFalse
      End if
End Function  
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:sumod_jacob
ID: 36951285
Pls..can anybody respond...
0
 

Author Comment

by:sumod_jacob
ID: 36951350
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?..
0
 

Author Comment

by:sumod_jacob
ID: 36995216
Finaly i have created User DSN to solve the issue. System DSN required Administrator rights to configure.
0
 

Author Closing Comment

by:sumod_jacob
ID: 36995233
Not got the proper answer for my question
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

864 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