Black_Flag
asked on
SQL ODBC Batch file question
Question...I have a bat file I wrote to create an ODBC connection, but I wanted to set it up with SA user authentication. How do I add that into my current code along with the password?
cd C:\WINDOWS\system32
"odbcconf.exe" /a {CONFIGSYSDSN "SQL Server" "DSN=tkcsdb|Description=tkcsdb|SERVER=vsapp9|Network=MAHC|Trusted_Connection=Yes|Database=tkcsdb"}
pause
@CLS
@EXIT
It should be something like this:
I usually build a file and load it via regedit:
You can read more about it here: Creating DSNs for SQL Server: In depth on how to make one.
cd C:\WINDOWS\system32
"odbcconf.exe" /a {CONFIGSYSDSN "SQL Server" "DSN=tkcsdb|Description=tkcsdb|SERVER=vsapp9|Network=MAHC|Trusted_Connection=No|LastUser=sa|Database=tkcsdb"}
pause
@CLS
@EXIT
I usually build a file and load it via regedit:
Windows Registry Editor Version 5.00
[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources]
"ExampleDSN"="SQL Server"
[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ExampleDSN]
"Driver"="C:\\WINDOWS\\system32\\SQLSRV32.dll"
"Description"="To give details"
"Server"="ServerName"
"QuotedId"="No"
"AnsiNPW"="No"
"Database"="AdventureWorks"
"LastUser"="sa"
"AutoTranslate"="No"
You can read more about it here: Creating DSNs for SQL Server: In depth on how to make one.
ASKER
THanks! Is there a spot where I have to put a password in though or no?
You can't save the password in the ODBC call. That has to be handled by your application. As an example if your were to link from an MS Access db, there is a hidden table called MSysobjects. The Connect column would look something like this:
DSN=TCL;UID=AppMGR;PWD=password;APP=Microsoft Office XP;AutoTranslate=No;QuotedId=No;AnsiNPW=No
In reference to : 37354575
You could also try adding the key:
"PWD"="ThePassword"
Let me know if it works.
You could also try adding the key:
"PWD"="ThePassword"
Let me know if it works.
ASKER
So you mean something like this?
cd C:\WINDOWS\system32
"odbcconf.exe" /a {CONFIGSYSDSN "SQL Server" "DSN=tkcsdb|Description=tkcsdb|SERVER=vsapp9|Network=MAHC|Trusted_Connection=No|LastUser=sa|PWD=password|Database=tkcsdb"}
pause
@CLS
@EXIT
ReneGe,
I tried that in the past. The SQL Server and SQL Native Client drivers are designed to deliberately to ignore it. Some other drivers can and do support that withe either plain text or encrypted passwords.
Would you want every person that had access to the registry to be able to see your plain text password?
I tried that in the past. The SQL Server and SQL Native Client drivers are designed to deliberately to ignore it. Some other drivers can and do support that withe either plain text or encrypted passwords.
Would you want every person that had access to the registry to be able to see your plain text password?
ASKER
I just realised it was about "SQL Server" not "MySQL".
Sorry for that ;(
Sorry for that ;(
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER