jana
asked on
How to use odbc in vb to connect to ms sql
Is there a way to embed the odbc connebction in vb code in order to connect to ms sql instance that pertain to a domain? Please advice ok how-to.
ASKER
currently we use:
This is in our vb6 code.
Unfortunately we noticed that if the Windows user connecting to the domain is not created in the SQL server the apps will not connect. The reason we ask if we can embed the odbc connection within our vb6 code is because the users uses odbc to connect to the SQL server while entering the domain with a Windows user not created in SQL (that is, different username).
Is there a way to use odbc drivers connection within vd code?
cnn.Open "Provider=SQLOLEDB; " & _
"Initial Catalog=" & CompanyDB & "; " & _
"Data Source=" & ServerName & "; " & _
"integrated security=SSPI; persist security info=True;"
This is in our vb6 code.
Unfortunately we noticed that if the Windows user connecting to the domain is not created in the SQL server the apps will not connect. The reason we ask if we can embed the odbc connection within our vb6 code is because the users uses odbc to connect to the SQL server while entering the domain with a Windows user not created in SQL (that is, different username).
Is there a way to use odbc drivers connection within vd code?
Unfortunately we noticed that if the Windows user connecting to the domain is not created in the SQL server the apps will not connect.Then don't use the Integrated Security parameter. Instead provide the SQL login:
cnn.Open "Provider=SQLOLEDB; " & _
"Initial Catalog=" & CompanyDB & "; " & _
"Data Source=" & ServerName & "; " & _
"UID=" & SqlLogin & ";PWD=" & Password &_
"; persist security info=True;"
ASKER
Yes, prior placing the question we tried supplying login/password and it does work, but the problem is that there are over 30 users and coding to ask for SQL password is not feasible for them (currently they don't enter any password when running their apps via odbc connections so it would be something additional in thier daily task).
We are assuming that there is no way to use odbc within a connection within our code?
We are assuming that there is no way to use odbc within a connection within our code?
(currently they don't enter any password when running their apps via odbc connections so it would be something additional in thier daily task).Can you check if the user credentials are stored in the ODBC? It's the only way I can see for not requiring them to introduce the user credentials.
We are assuming that there is no way to use odbc within a connection within our code?The way I see it is only with the Trusted Connection but for that you'll need to create the necessary logins in the SQL Server instance. An easy way to accomplish this is to have an AD group where all those users are included and provide only permissions for that AD group in the SQL Server instance so any person that is member of the AD group will have automatically access to the database.
ASKER
Yes, user credentials are stored in the ODBC.
ASKER
We found a link where it shows (somewhat) to connect to a SQL server via ODBC or DSN (see here and go find 'macleod1021'):
unfortunately, it gives an error:
And the odbc is:
The odbc has the user and password. We know that using ODBC is not used too much now a days and even slower than ADO, but the site that what is uses.
Please advice on how to use a DNS in our VB code to connect to their SQL database.
Dim cn As adodb.Connection
Dim rs As adodb.Recordset
Dim str As String
Set cn = New adodb.Connection
cn.ConnectionString = "DSN=ODBC_NAME_CURRENTLY_USED"
cn.Open
Set rs = New adodb.Recordset
str = "Select * from tblTest"
rs.Open str, cnn, adOpenKeyset, adLockOptimistic
rs.Close
cn.Close
unfortunately, it gives an error:
And the odbc is:
The odbc has the user and password. We know that using ODBC is not used too much now a days and even slower than ADO, but the site that what is uses.
Please advice on how to use a DNS in our VB code to connect to their SQL database.
The DSN name is D201 and is configured in ODBC 32bit.
There's also ODBC 64bit and depending on your machine architecture the .NET may use the 64bit instead of the 32bit. Can you confirm that?
There's also ODBC 64bit and depending on your machine architecture the .NET may use the 64bit instead of the 32bit. Can you confirm that?
ASKER
The DSN name is D201 and is configured in ODBC 32bit. Note, the machines have 64 bit but they use the 32 bit one.
ASKER
Happy New Year’s EE and may 2017 be even more prosperous & successful 4 u Guys!! Thanx Lots for all your help in 2016!!! God Bless!
You and VItor are wrong in thinking you have provided user and password in the ODBC DSN in a usable way. What you can leave there as user and password is only for checking at setup, e.g. which databases are available to be able to pre-select one.
If using SQL Authentication, you always have to provide the login credentials when opening the connection. It does not matter if you use ODBC, ADO, OLE DB or anything else.
If using SQL Authentication, you always have to provide the login credentials when opening the connection. It does not matter if you use ODBC, ADO, OLE DB or anything else.
ASKER
Understood.
To further provide EE info in order to help with our question, let’s give you our scenario:
• The users uses MS Dynamics Apps on a daily basis.
• The apps uses an ODBC connection.
• When we first setup a user to use the MS Dynamics Apps, we have to create in their PC an ODBC connection to the SQL Server where MS Dynamics databases are located.
• If we want a user to use the MS Dynamics apps, we have to first create an ODBC connection to apps and then create a MS Dynamics apps working-user within the apps.
• When creating the ODBC connection for the first time, we use the user SA and it's password to, as you point out, "checking at setup".
• We then connect to the MS Dynamics apps using the ODBC connection.
• The MS Dynamics apps requests a user to enter for working with the apps.
• Since we need said working-user to work with MS Dynamics apps, we enter the SA user and the SA password, so as admin within the MS Dynamics apps we can create the working user.
• After creating the working-user within the MS Dynamics apps, we notices 2 things:
o The MS Dynamics apps creates a user in two areas within SQL Server; in ‘SECURITY >> LOGIN’ and also in ‘DATABASE >> SECURITY >> USERS’.
o Also the actual domain user that is used to login to the work station & domain is not created within SQL Server (for example, the domain everyday user is JOE-SHMOE and the MS Dynamics Apps user created is JOE).
• Now the user is ready to user the MS Dynamics apps.
• In order for the user to work with the MS Dynamics apps, the user has to enter the apps with working-user/password created (the user doesn’t user SA to work with the apps) – as a matter of fact, any user created to use MS Dynamics apps, can enter in any PC to work properly.
Our VB6 scenario:
• We created a VB6 apps that is called with by the user when inside working with the MS Dynamics Apps.
• The connection within the VB6 is:
The problem:
If the user domain is not created within the SQL server, the VB6 apps doesn’t work.
The Question:
Is there a way to embed the actual ODBC Connebction in our VB code in order to connect to ms sql instance that pertain to a domain?
To further provide EE info in order to help with our question, let’s give you our scenario:
• The users uses MS Dynamics Apps on a daily basis.
• The apps uses an ODBC connection.
• When we first setup a user to use the MS Dynamics Apps, we have to create in their PC an ODBC connection to the SQL Server where MS Dynamics databases are located.
• If we want a user to use the MS Dynamics apps, we have to first create an ODBC connection to apps and then create a MS Dynamics apps working-user within the apps.
• When creating the ODBC connection for the first time, we use the user SA and it's password to, as you point out, "checking at setup".
• We then connect to the MS Dynamics apps using the ODBC connection.
• The MS Dynamics apps requests a user to enter for working with the apps.
• Since we need said working-user to work with MS Dynamics apps, we enter the SA user and the SA password, so as admin within the MS Dynamics apps we can create the working user.
• After creating the working-user within the MS Dynamics apps, we notices 2 things:
o The MS Dynamics apps creates a user in two areas within SQL Server; in ‘SECURITY >> LOGIN’ and also in ‘DATABASE >> SECURITY >> USERS’.
o Also the actual domain user that is used to login to the work station & domain is not created within SQL Server (for example, the domain everyday user is JOE-SHMOE and the MS Dynamics Apps user created is JOE).
• Now the user is ready to user the MS Dynamics apps.
• In order for the user to work with the MS Dynamics apps, the user has to enter the apps with working-user/password created (the user doesn’t user SA to work with the apps) – as a matter of fact, any user created to use MS Dynamics apps, can enter in any PC to work properly.
Our VB6 scenario:
• We created a VB6 apps that is called with by the user when inside working with the MS Dynamics Apps.
• The connection within the VB6 is:
cnn.Open "Provider=SQLOLEDB; " & _
"Initial Catalog=" & CompanyDB & "; " & _
"Data Source=" & ServerName & "; " & _
"integrated security=SSPI; persist security info=True;"
• When the user clicks on the VB6 apps we use their actual MS Dynamics Apps user within our code; thus, there is no second login to this VB6 apps since it is using the above connection.The problem:
If the user domain is not created within the SQL server, the VB6 apps doesn’t work.
The Question:
Is there a way to embed the actual ODBC Connebction in our VB code in order to connect to ms sql instance that pertain to a domain?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ah yes!!! Great idea to create a domain group!! Just finish running the idea with the group and it viable solution, Thanx!
Open in new window