Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
weshader
Starting Member
2 Posts |
 Posted - 2012-07-05 : 16:36:55
|
Hello, As I'm sure you hear all the time, I am fairly new to SQL, so please excuse my ignorance.  I'll start by explaining that I have built several different Access databases, and I have just recently started upsizing them to a SQL server for improved performance. I have successfully finished the upsizing and have no problems using the Access front end db's. My issue began when I started contemplating how to set up each individual System DSN on the workstations. I started searching on how to do this via command line. I found the following command, which I have put into a .bat file.[CODE]ODBCCONF.exe CONFIGSYSDSN "SQL Server" "DSN=Logsheet_Columbia | Description=Connection to Logsheet Tables | SERVER=MCLDB1\MCLSQLSERVER | Trusted_Connection=Yes | Database=SQL_Logsheet_Columbia"[/CODE]The good news is that the DSN is created if I run the .bat file as an Administrator. The bad news is that the connection fails until I open the DSN and physically select the SQL Server from the drop down list in the configuration window. If I leave the value created by the .bat file, which IS spelled correctly, I get the following error. Connection failed:SQLState: '01000'SQL Server Error: 67[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen(Connect()).Connection Failed:SQLState: '08001'SQL Server Error: 17[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access is denied.Once I physically select the server, all is well, and I can open the front end db without any errors.I am trying to avoid the need to configure each individual's DSN, so any help would be GREATLY appreciated! I am using SQL Server 2008 with Windows 7 Professional x64. My SQL Server is using an NT Group for security on the database and the Domain Admin, which creates the DSN, is part of that group. Thanks! |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-07-05 : 16:45:47
|
| Best advice I can give: go DSN-less. That way you don't have to set up anything on a client computer. There are details here:http://support.microsoft.com/kb/892490It looks scarier than it actually is. The main thing to remember is a basic ODBC connection string:ODBC;DRIVER=SQL Server;SERVER=serverName;DATABASE=databaseName;Trusted_Connection=YesorODBC;DRIVER=SQL Server;SERVER=serverName;DATABASE=databaseName;UID=userName;PWD=passwordThe code listed in the link automates the process for MS Access. |
 |
|
|
weshader
Starting Member
2 Posts |
Posted - 2012-07-06 : 10:11:40
|
| Thanks! I am now DSNless, and everything is working great! |
|
|
|
|
|
|
|
|
Adding SQL Server System DSN via command line