Link to home
Start Free TrialLog in
Avatar of jana
janaFlag for United States of America

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.
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Do you want to know the .net code to connect to a SQL Server database?
Dim conn As OdbcConnection

conn = New OdbcConnection("DRIVER={SQL Server};SERVER=SQLServerInstance;UID=sqlloginname;PWD=password;DATABASE=databasename")

conn.Open()
MsgBox("I have connected to my SQL Server database")
conn.Close()

Open in new window

Avatar of jana

ASKER

currently we use:

cnn.Open "Provider=SQLOLEDB; " & _
          "Initial Catalog=" & CompanyDB & "; " & _
          "Data Source=" & ServerName & "; " & _
          "integrated security=SSPI; persist security info=True;"

Open in new window


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;"

Open in new window

Avatar of jana

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?
(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.
Avatar of jana

ASKER

Yes, user credentials are stored in the ODBC.
Avatar of jana

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'):

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

Open in new window


unfortunately, it gives an error:
 User generated image
And the odbc is:
User generated image
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?
Avatar of jana

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.
Avatar of jana

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.
Avatar of jana

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:
cnn.Open "Provider=SQLOLEDB; " & _
          "Initial Catalog=" & CompanyDB & "; " & _
          "Data Source=" & ServerName & "; " & _
          "integrated security=SSPI; persist security info=True;"

Open in new window

•      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
Avatar of Qlemo
Qlemo
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of jana

ASKER

Ah yes!!! Great idea to create a domain group!! Just finish running the idea with the group and it viable solution, Thanx!