PDA

View Full Version : ODBC System DSN for SQL Server



Perucho
07-29-2004, 12:15 PM
Object ODBC 3.51 - Using the ODBC Wizard, how do you create an ODBC System DSN for SQL Server with attributes initialized (most importantly the attributes LoginID and Password to connect with SQL Server)? My problem is that the ODBC Wizard only sees already available data sources?

I know how to initialize attributes using oObj.InstallDataSource(), but LoginID and Password are not working. Any ideas?

helmut
07-30-2004, 06:52 AM
I had to create an ODBC system datasource too. Therefore I created a registry set, which has dependencies to the relevant components.

There are two possibilities, depending on the authorization mode of SQL Server.

1. Authentication by Windows user


REGEDIT4

[HKEY_LOCAL_MACHINE]

[HKEY_LOCAL_MACHINE\SOFTWARE]

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC]

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI]

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\eurotrans]
"Description"="Eurotrans Database"
"Database"="<EUROTRANS_DBS_DATABASENAME>"
"Server"="<EUROTRANS_DBS_SERVERNAME>"
"LastUser"="Administrator"
"Driver"="<WINSYSDIR>\\SQLSRV32.dll"
"Trusted_Connection"="Yes"

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources]
"eurotrans"="SQL Server"


2. Authentication by SQL Server user


REGEDIT4

[HKEY_LOCAL_MACHINE]

[HKEY_LOCAL_MACHINE\SOFTWARE]

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC]

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI]

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\eurotrans]
"Description"="Eurotrans Database"
"Database"="<EUROTRANS_DBS_DATABASENAME>"
"Server"="<EUROTRANS_DBS_SERVERNAME>"
"LastUser"="sa"
"Driver"="<WINSYSDIR>\\SQLSRV32.dll"

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources]
"eurotrans"="SQL Server"


Note that I have used some text substitutions instead of literals.

Perucho
07-30-2004, 11:52 AM
I need to use the ODBC Object Wizard to create an ODBC System DSN for SQL Server with attributes UID and PWD initialized. Is this possible?

First problem, is that the ODBC Ojbect Wizard needs to have a DSN already in the system (in the registry).

Second problem, for some reason (security), I don't think initializing UID and PWD is allowed. UID is LastUser in this case.
If the password is not there, will the system prompt the user to enter password? What if you want to include the password?

Any ideas?

Ingreyd
08-12-2004, 08:54 PM
I have the ODBC Object in my project and have added the following routine. This add a System DSN to the target system and sets the user and password. (This only works if the SQL Server is using Mixed Mode Security)

function AddDSN(sDSNName, sSQLServer, sSQLDatabase, sSQLPassword, sSQLUserID)
OBJECT oObj;
STRING szAttribs, szDSNType, szName;
BOOL bUninstall;
begin
//the parameter of this first function should be identical to the name of the ODBC object displayed in the components tab

set oObj = GetObject("New ODBC 3.51 1");

//set the parameters for the InstallDataSource function

szAttribs= "Driver=\\sqlsrv32.dll\n" + "DriverName=SQL Server\n";
szDSNType = "system"; //The DSN type can be either "user" or "system"
szName = sDSNName; //The DSN name you want displayed in the ODBC Administrator
bUninstall = TRUE;

if ( oObj.InstallDataSource ( szAttribs, szDSNType, szName, bUninstall ) <0) then
MessageBox("The DSN didn't install correctly",0);
else
//MessageBox("The DSN successfully installed",0);
RegDBSetDefaultRoot (HKEY_LOCAL_MACHINE);
RegDBSetKeyValueEx ("SOFTWARE\\ODBC\\ODBC.INI\\" + sDSNName, "Database", REGDB_STRING, sSQLDatabase, -1);
RegDBSetKeyValueEx ("SOFTWARE\\ODBC\\ODBC.INI\\" + sDSNName, "Server", REGDB_STRING, sSQLServer, -1);
RegDBSetKeyValueEx ("SOFTWARE\\ODBC\\ODBC.INI\\" + sDSNName, "LastUser", REGDB_STRING, sSQLUserID, -1);
endif;

end;

Perucho
08-17-2004, 12:39 PM
Thanks for all the help.

set oObj = GetObject("New ODBC 3.51 1");

I was able to create the system DSN by using GetObject(), InstallDataSource () and passing all the attributes. But I found out that 'LastUser' eventhough is an attribute that gets passed through InstallDataSource(), it does not get initialized in the registry for some odd reason.
Therefore 'LastUser' is the only attribute I had to add it through the registry, since InstallDataSource() did not do it.

RegDBSetDefaultRoot (HKEY_LOCAL_MACHINE);
RegDBSetKeyValueEx ("SOFTWARE\\ODBC\\ODBC.INI\\" + sDSNName, "LastUser", REGDB_STRING, sSQLUserID, -1);

DSN Types:
1. System DSN
2. User DSN
3. File DSN
4. DSN-Less connection

You can get the password by using a dialog window and asking the user to enter it, or you can hardcoded in a variable.

Note: AFAIK, you don't need the password to create a DSN, but only to test the DSN connection to the database. The DSN password cannot be saved in the registry (afaik).

If you need to stored the password, then use a File DSN, but is not secured.

If you don't need to stored the password, you can use it to test your dsn connection.

Then use odbcping.exe to test DSN connection.
usage: odbcping [-S Server | -D DSN] [-U Login Id] [-P Password]


You can create a DSN using:
1. Using GetObject() and InstallDataSource() (needs an object)
2. Using Registry functions, add the DSN by setting Windows Registry keys (does NOT need an object)
3. File DSN (can have all attributes including UID and PWD, but is not secured since is a text file)
4. DSN-Less connection

Useful Links:
1. Using ODBC with Microsoft SQL Server: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnodbc/html/odbcsql.asp
2. The Problem with ODBC: http://www.databasejournal.com/features/mssql/article.php/1491051
3. What is DSN-Less ODBC? http://www.databasejournal.com/features/mssql/article.php/1491011

ThatDennis
11-11-2004, 09:52 AM
set oObj = GetObject("New ODBC 3.51 1");
I'm pretty new at all of this, and I've searched the fora pretty extensively, but hopefully someone can explain the following to me;

As I understand it GetObject() creates an instance of an object already inserted in the components view and therefore available for instancing. How do I add the ODBC object to those components? I can't for the life of me figure out how to do it, and simply running the command quoted above will leave me with an empty object since can't instance it.

If anyone can help me out, I'd appreciate it.

Perucho
11-11-2004, 12:24 PM
For an InstallScript Project:

go to the "Installation Designer"
go to "Application Data"
go to "Objects"
scroll down the object lists to "ODBC 3.51"
to add the object to your feature
click and drag it to the bottom window where you have your selected feature
or right click on the "ODBC 3.51" object and select "Add to selected feature"

ThatDennis
11-11-2004, 12:46 PM
Thank you for your fast reply, Perucho. Now, I have to admit that I'm using an InstallScript MSI project, first of all, and second, that I can't find the ODBC object in the Object list of the InstallScript project, or the Redistributables list of the InstallScript MSI project. However, in the InstallScript project, in the object list, I do see an MDAC 2.8, however.

A few questions;
1) Can I use the MDAC 2.8 instead of the ODBC object? If not how I do I go about adding the ODBC object to the list of selectable objects?
2) Is what I'm trying to achieve only possible in projects that have an object listing under the Application Data folder in the Installation Designer? Or can I do the same in projects that only show Redistributables?

Thanks for the help so far.

Perucho
11-11-2004, 12:59 PM
What version of InstallShield are you using?
InstallShield X? or InstallShield X with SP1?

To add more objects, use the "Program Updates" from InstallShield.

I believe the ODBC 3.51 Object comes as default with your installation of InstallShield X. You don't need IS X SP1 to have this object in the list.

Did you do a full installation of InstallShield X?

ThatDennis
11-11-2004, 02:24 PM
While I know I'm posting in the wrong forum, I'm using InstallShield 10.5 Professional Edition, and the evaluation version at that. I'm trying to determine if our company should purchase IS and I'm trying to learn my way around a bit. This is one of the problems I stumbled across and it had only been discussed in this thread. I apologise for the confusion.

Does this make a difference?

Perucho
11-11-2004, 03:20 PM
I am not using InstallShield 10.5 PE yet, so I don't know if this object ("ODBC 3.51") should be on the list of objects by default.

Try adding more objects with the "Program Updates" tool from InstallShield, or go to "Tools" and select "Check for Updates" and install the additional InstallScript Objects.

It may be that the Evaluation Version is limited and does not have all the objects.


I found this on IS Website. Try to install these executables.

InstallShield 10.5
http://www.installshield.com/downloads/default.asp?pm=isx&ped=xprow&pver=10.50&gv=1&msxmlhidden=y&mt=All&Language=English&platform=&mode=submit&image.x=53&image.y=11

InstallShield 10.5 InstallScript Objects
http://www.installshield.com/downloads/release.asp?xmlUse=y&releaseid=2466&target=http%3A%2F%2Fsaturn%2Einstallshield%2Ecom%2Fis%2F10%2E5%2Fobjects%2Fscript%2Finstallshield1050installscriptobjects%2Eexe

InstallShield 10.5 Legacy InstallScript Objects
http://www.installshield.com/downloads/release.asp?xmlUse=y&releaseid=2465&target=http%3A%2F%2Fsaturn%2Einstallshield%2Ecom%2Fis%2F10%2E5%2Fobjects%2Fscript%5Flegacy%2Finstallshield1050legacyinstallscriptobjects%2Eexe

InstallShield 10.5 MSI Objects
http://www.installshield.com/downloads/release.asp?xmlUse=y&releaseid=2467&target=http%3A%2F%2Fsaturn%2Einstallshield%2Ecom%2Fis%2F10%2E5%2Fobjects%2Fmsi%2Fsetup%2Eexe

InstallShield 10.5 Skin Customization Kit
http://www.installshield.com/downloads/release.asp?xmlUse=y&releaseid=2468&target=http%3A%2F%2Fsaturn%2Einstallshield%2Ecom%2Fis%2F10%2E5%2Fwindows%2Fskins%2Fsetup%2Eexe

ThatDennis
11-12-2004, 04:23 AM
Thanks for your help. I'll let you know how it goes!

eposty
05-03-2005, 09:36 AM
I have the ODBC Object in my project and have added the following routine. This add a System DSN to the target system and sets the user and password. (This only works if the SQL Server is using Mixed Mode Security)

function AddDSN(sDSNName, sSQLServer, sSQLDatabase, sSQLPassword, sSQLUserID)
OBJECT oObj;
STRING szAttribs, szDSNType, szName;
BOOL bUninstall;
begin
//the parameter of this first function should be identical to the name of the ODBC object displayed in the components tab

set oObj = GetObject("New ODBC 3.51 1");

//set the parameters for the InstallDataSource function

szAttribs= "Driver=\\sqlsrv32.dll\n" + "DriverName=SQL Server\n";
szDSNType = "system"; //The DSN type can be either "user" or "system"
szName = sDSNName; //The DSN name you want displayed in the ODBC Administrator
bUninstall = TRUE;

if ( oObj.InstallDataSource ( szAttribs, szDSNType, szName, bUninstall ) <0) then
MessageBox("The DSN didn't install correctly",0);
else
//MessageBox("The DSN successfully installed",0);
RegDBSetDefaultRoot (HKEY_LOCAL_MACHINE);
RegDBSetKeyValueEx ("SOFTWARE\\ODBC\\ODBC.INI\\" + sDSNName, "Database", REGDB_STRING, sSQLDatabase, -1);
RegDBSetKeyValueEx ("SOFTWARE\\ODBC\\ODBC.INI\\" + sDSNName, "Server", REGDB_STRING, sSQLServer, -1);
RegDBSetKeyValueEx ("SOFTWARE\\ODBC\\ODBC.INI\\" + sDSNName, "LastUser", REGDB_STRING, sSQLUserID, -1);
endif;

end;

I have a question about this... Is there anyway to, when creating the DSN, to uncheck "Connect to SQL Server to obtain Default Settings", "User ANSI nulls, paddings, and warnings" and "Perform Translation for Character Data" while installing the DSN via InstallShield X?

Perucho
05-05-2005, 03:26 PM
Create the DSN through the registry and apply the right parameters





[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\DSName]
"Database"="DBname"
"Driver"="C:\\WINNT\\system32\\sqlsrv32.dll"
"LastUser"="sa"
"Server"="ServerName"
"UseProcForPrepare"="0"
"Regional"="Yes"
"Fallback"="Yes"
"Encrypt"="Yes"
"QuotedId"="No"
"AnsiNPW"="No"
"AutoTranslate"="No"




Using ODBC with Microsoft SQL Server
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnodbc/html/odbcsql.asp


DATABASE
This parameter specifies the default database for the ODBC data source.

LANGUAGE
This parameter specifies the default national language to use.

OEMTOANSI
This parameter specifies whether to convert extended characters to OEM values.

SQL Server is usually run with one of three code pages:

437 code page.
The default code page for U.S. MS-DOS computers.

850 code page.
The code page typically used by UNIX systems.

ISO 8859-1 (Lantin1 or ANSI) code page.
The code page defined as a standard by the ANSI and ISO standards organizations. The default code page for U.S. Windows computers. Sometimes called the 1252 code page.

The 437 and 850 code pages are sometimes collectively referred to as the OEM code pages.

All three code pages define 256 different values to use in representing characters. The values from 0 to128 represent the same characters in all three code pages. The values from 129 to 255, which are known as the extended characters, represent different characters in all three code pages.

Because ODBC applications are Windows applications, they generally use ANSI code page 1252. If they are communicating with a SQL Server also running ANSI code page 1252, there is no need for character-set conversion. If they connect to a server running a 437 or 850 code page however, the driver must be informed that it should convert extended characters from their 1252 values to 437 or 850 values before sending them to the server. In this case, the data source should have OEMTOANSI=YES. For a more in-depth discussion of SQL Server code pages, see Microsoft Knowledge Base article Q153449.

TRANSLATIONDLL
This parameter specifies the name of the ODBC translation DLL to use with the data source.

TRANSLATIONNAME
This parameter specifies the name of the translator to use with the data source.

TRANSLATIONOPTION
This parameter specifies whether translation should be done on the data going to SQL Server. YES specifies translation; NO specifies no translation. For more information about ODBC translation, see the ODBC 2.0 Programmer's Reference.

USEPROCFORPREPARE
This parameter specifies whether the driver generates stored procedures to support the ODBC SQLPrepare function. For more information, see "SQLExecDirect vs. SQLPrepare/SQLExecute."

The following driver-specific SQLConfigDataSource keywords are new in SQL Server 6.5 SP2.

QuotedID
This parameter specifies whether the driver should issue a SET QUOTED IDENTIFIERS ON option when connecting to a SQL Server version 6.0 or later database. YES specifies QUOTED_IDENTIFIERS is ON; NO specifies the option is OFF. For more information, see "SET Options Used by the Driver."

AnsiNPW
This parameter specifies whether the driver should SET ON the ANSI_NULLS, ANSI_PADDING, and ANSI_WARNINGS options when connecting to a SQL Server version 6.5 or later database. YES specifies the options are ON; NO specifies they are OFF. For more information, see "SET Options Used by the Driver."

The following driver-specific SQLConfigDataSource keywords are new in SQL Server 6.5.

QueryLogFile
This parameter specifies the file name the driver should use to log long-running queries. Include the full path name for the file. For more information, see "ODBC Driver Profiling Features."

QueryLog_ON
This parameter specifies whether the data source should do query profiling. 1 specifies profiling is done; omitting the parameter specifies no profiling. For more information, see "ODBC Driver Profiling Features."

QueryLogTime
This parameter specifies the interval for long-running queries. The interval is specified in milliseconds. If a query is outstanding for a period exceeding the QueryLogTime, it is written to the QueryLogFile. For more information, see "ODBC Driver Profiling Features."

StatsLogFile
This parameter specifies the file name the driver should use to log long performance statistics. Include the full path name for the file. For more information, see "ODBC Driver Profiling Features."

StatsLog_On
This parameter specifies whether the data source should log performance statistics. 1 specifies profiling is done; omitting the parameter specifies no profiling. For more information, see "ODBC Driver Profiling Features."

Trusted_Connection
This parameter specifies whether the data source should use trusted connections when connecting to SQL Server. 1 specifies trusted connections; omitting the parameter specifies no trusted connections. For more information, see "Integrated and Standard Security."

Bill Carlisle
05-10-2005, 01:48 PM
Has anyone ever done an Oracle Install setup through ISPro6.0?

I was hoping to compare notes...

I have it working but I'm wondering why my RegDBKeyExist check is always finding the key "existing" even when it is a definate BAD key...

if (LaunchAppAndWait (SRCDISK ^ "Disk1\\Setup.exe", "-responsefile \""+RSP_FILE+"\" -silent", WAIT) < 0) then
gszLogMsg = "Unable to launch Oracle Setup.exe";
WriteToLog(gszLogMsg);
MessageBox (gszLogMsg,SEVERE);
else
nPercent = 1;
gszLogMsg = "Oracle is now installing, this may take up to 15-20 minutes";
SetStatusWindow(nPercent, gszLogMsg);
WriteToLog(gszLogMsg);

Enable(STATUS);
RegDBSetDefaultRoot(HKEY_LOCAL_MACHINE);
szKey = "SYSTEM\\CurrentControlSet\\Services\\OracleServicePAIN";
szKey1 ="SOFTWARE\\Microsoft\\Windows\\CurrentVersion\\Uninstall\\{D5946AF2-3399-11D7-AFDD-E5BE1EDF9FCB}";
szKey2 ="SYSTEM\\ControlSet001\\Services\\OraclePAINPagingServer";
szKey3 ="SYSTEM\\CurrentControlSet\\Services\\EventLog\\Application\\OracleAgent";
szKey4 ="SOFTWARE\\Classes\\TypeLib\\{0BB9AFD1-51A1-11D2-BB7D-00C04FA30080}\\1.0\\0\\win32";
szKey5 ="SOFTWARE\\HQ AFPC\\ManperB Oracle Installer";
szKey6 ="SYSTEM\\ControlSet001\\Control\\Session Manager\\Environment";
szKey7 ="SOFTWARE\\Classes\\CLSID\\{51A5FC60-13DF-11CF-BE51-0020AFE55F5C}\\InprocServer32";
szKey8 ="SOFTWARE\\Classes\\MMDX.MmdxCtrl.1";
szKey9 ="SOFTWARE\\Classes\\CLSID\\{3893B4A0-FFD8-101A-ADF2-04021C007002}";
szKey10="SOFTWARE\\Classes\\CLSID\\{3F63C36E-51A3-11D2-BB7D-00C04FA30080}";
szKey11="SOFTWARE\\ORACLE";
bvKey1 = FALSE;
bvKey2 = FALSE;
bvKey3 = FALSE;
bvKey4 = FALSE;
bvKey5 = FALSE;
bvKey6 = FALSE;
bvKey7 = FALSE;
bvKey8 = FALSE;
bvKey9 = FALSE;
bvKey10 = FALSE;
bvKey11 = FALSE;


// wait until the instance service is installed
svMsg = "Oracle installing - Estimated time remaining is ";

gszLogMsg = "Starting 1st Loop looking for ["+szKey+"]";
WriteToLog(gszLogMsg);

nMinutesRemain = 6;
nSecondsRemain = 0;
nTotalSeconds = (nMinutesRemain*60) + nSecondsRemain;
while nMinutesRemain > 0
// Percentage bar meter
nPercent = nPercent + 1;
nResults = NumToStr (svPercent, nPercent);
bvFOUND = FALSE;

if (!(bvKey1) && (RegDBKeyExist(szKey1)=1)) then
bvKey1 = TRUE;
gszLogMsg = szKey1;
bvFOUND = TRUE;
elseif !(bvKey2) && RegDBKeyExist(szKey2) then
bvKey2 = TRUE;
gszLogMsg = szKey2;
bvFOUND = TRUE;
elseif !(bvKey3) && RegDBKeyExist(szKey3) then
bvKey3 = TRUE;
gszLogMsg = szKey3;
bvFOUND = TRUE;
elseif !(bvKey4) && RegDBKeyExist(szKey4) then
bvKey4 = TRUE;
gszLogMsg = szKey4;
bvFOUND = TRUE;
elseif !(bvKey5) && RegDBKeyExist(szKey5) then
gszLogMsg = szKey5;
bvKey5 = TRUE;
bvFOUND = TRUE;
elseif !(bvKey6) && RegDBKeyExist(szKey6) then
gszLogMsg = szKey6;
bvKey6 = TRUE;
bvFOUND = TRUE;
elseif !(bvKey7) && RegDBKeyExist(szKey7) then
gszLogMsg = szKey7;
bvKey7 = TRUE;
bvFOUND = TRUE;
elseif !(bvKey8) && RegDBKeyExist(szKey8) then
gszLogMsg = szKey8;
bvKey8 = TRUE;
bvFOUND = TRUE;
elseif !(bvKey9) && RegDBKeyExist(szKey9) then
gszLogMsg = szKey9;
bvKey9 = TRUE;
bvFOUND = TRUE;
elseif !(bvKey10) && RegDBKeyExist(szKey10) then
gszLogMsg = szKey10;
bvKey10 = TRUE;
bvFOUND = TRUE;
elseif !(bvKey11) && RegDBKeyExist(szKey11) then
gszLogMsg = szKey11;
bvKey11 = TRUE;
bvFOUND = TRUE;
endif;
if bvFOUND then
gszLogMsg = "Found ["+gszLogMsg+"] %["+svPercent+"]";
else
gszLogMsg = "No Hit on Installed Keys";
endif;
WriteToLog(gszLogMsg);

WriteToLog("Starting 10 second delay");
// Remaining time text meter - smaller increments
// 5 increments x 2 seconds each = 10 seconds delay.
for iCount = 1 to 5
nResults = NumToStr (svMinutesRemain, nMinutesRemain);
nResults = NumToStr (svSecondsRemain, nSecondsRemain);
svTimeRemain = svMinutesRemain + ":" + svSecondsRemain + " minutes.";

nResults = NumToStr (svPercent, nPercent);
gszLogMsg = svPercent+"% "+ svMsg + svTimeRemain;
WriteToLog(gszLogMsg);

SetStatusWindow(nPercent, svMsg + svTimeRemain);
Delay(2);
nTotalSeconds = nTotalSeconds - 2;
nMinutesRemain = nTotalSeconds/60;
nSecondsRemain = nTotalSeconds - (nMinutesRemain*60);
endfor;
WriteToLog("Ending 10 second delay");
endwhile;

Bill :)

Bill Carlisle
05-20-2005, 09:20 AM
Hi,
All seems to be working awesome!

Now I need to know when I should reboot and when not to...??

On the Oracle install component I know I need to reboot to start the services and the database... but what about others?

Do you need to reboot in Win2000 after registry creations or deletes?

Any rules or guide lines?

Thank you again for your help, Bill

Rifrafredo
12-10-2014, 08:14 AM
An easy way to do this is to create the System DSN connections needed while capturing an install with Repackager. Run your setup, config your ODBC connections and run the post scan. Works EVERY time with no scripting. If a stand-alone ODBC config needs to be distributed sans any other software, simply record the ODBC config with repackager and compile as an MSI. Simple!!