Better way of connecting to SQL 2005
-
So I am using ODBC to connect to my SQL server like such:
connection = "Provider=MSDASQL;" + "Driver={SQL Native Client};" + "Server=MYSERVER;" + "Database=MYDB;" + "UID=MYUSERNAME;" + "PWD=MYPASSWORD;" + Trusted_Connection=yes;" Try Using connection As New OdbcConnection(ConnectionString) Dim command As New OdbcCommand(queryString, connection) connection.Open() Dim reader As OdbcDataReader = command.ExecuteReader() While reader.Read() station_count = reader(0).ToString End While connection.Close() reader.Close() End Using Catch ex As Exception SQLERROR = True WriteErrorLog(ex.ToString) Exit Sub End TryThis is causing me a problem within my application. I wrote it using a Domain Admin which has access to the server through my SQL user, but the application needs to be deployed on a machine with much lower permission. My question is: 1. Should I rewrite the database connection using a different method instead of ODBC 2. Is there something I can do on the server to fix this? Thanks! Steve -
So I am using ODBC to connect to my SQL server like such:
connection = "Provider=MSDASQL;" + "Driver={SQL Native Client};" + "Server=MYSERVER;" + "Database=MYDB;" + "UID=MYUSERNAME;" + "PWD=MYPASSWORD;" + Trusted_Connection=yes;" Try Using connection As New OdbcConnection(ConnectionString) Dim command As New OdbcCommand(queryString, connection) connection.Open() Dim reader As OdbcDataReader = command.ExecuteReader() While reader.Read() station_count = reader(0).ToString End While connection.Close() reader.Close() End Using Catch ex As Exception SQLERROR = True WriteErrorLog(ex.ToString) Exit Sub End TryThis is causing me a problem within my application. I wrote it using a Domain Admin which has access to the server through my SQL user, but the application needs to be deployed on a machine with much lower permission. My question is: 1. Should I rewrite the database connection using a different method instead of ODBC 2. Is there something I can do on the server to fix this? Thanks! SteveWhy are you using the Odbc library instead of the SqlServer (SqlConnection, SqlCommand, Sqlxxxx...) specific library? Also, if you're using TrustedConnection, it means you're using the Windows account to login to SQL Server. Specifying a username and password with SSPI (Trusted Connection = True) is pointless since it won't be used. You either specify a username and password to use (SSPI off) SQL Servers authentication, or you don't specify them and turn SSPI on to use Windows Integrated security. You can get the various connection strings for all kinds of databases and connection options from ConnectionStrings.com[^]. There is also a small reference guide on the SQL Server connection strings here[^].
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007