CODEWITHSUNDEEP
sql-servervb.netdnsodbc
Parth Bhagat
Parth Bhagat

Reputation: 509

VB .NET ODBC Connection with Windows authentication

I am using Windows Server Datacenter. I am working on VB .Net 2010 Express Edition with .Net version 4.0.

I am having issue of making ODBC connection with SQL Server on VB .Net.

Here is my code snippet to connect with database.

Imports System.Data.Odbc

Public Class Form1

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim cn As Odbc.OdbcConnection
        cn = New Odbc.OdbcConnection("DRIVER={SQL Server};SERVER=<machine_name>\SQLEXPRESS;UID=<machine_name>\<windows_username>;" & _
                               "PWD=<windows_password>;DATABASE=testdb;")

        Dim mystring As String = "select * from Customers"
        Dim cmd As Odbc.OdbcCommand = New Odbc.OdbcCommand(mystring)
        cn.Open()
        MsgBox("Connected")
        cn.Close()
    End Sub
End Class

Running this program I am getting below error:

ERROR [28000] [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user '<machine_name>\<windows_username>'.

Primarily the issue sounds that the username and password provided is wrong. But I am using same username and password to access the system.

I think the issue is the way I have provided username and password to the connection string.

I have tried it by making DNS connection with the same database. I have created System DNS, and trying to connect with below connection string.

DRIVER={SQL Server};DNS=<DNS_name>;UID=<windows_username>;PWD=<windows_password>;

In this case I am getting following error.

ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

I have checked in both 32 bit and 64 bit DNS, the DNS name specified is available in both versions.

Where I am wrong to make connection? Is there any problem in specifying connection string? If so then please suggest correct connection string to make ODBC connection of SQL Server in VB.Net 2010 Express Edition .

Upvotes: 0

Views: 9731

Answers (1)

Valentin Petkov
Valentin Petkov

Reputation: 1648

http://support.microsoft.com/kb/310985
see point 4 for DSN
Add the following code to the DSN button: Dim cn as OdbcConnection cn = New OdbcConnection ("dsn=MyDSN;uid=sa;pwd=myPassword;") Dim mystring As String = "Select * from customers" Dim cmd As OdbcCommand = New OdbcCommand(mystring) cn.Open() MsgBox("Connected") cn.Close()

 Standard security
    Driver={SQL Server Native Client 11.0};Server=myServerAddress;
    Database=myDataBase;Uid=myUsername;Pwd=myPassword;

Are you using SQL Server 2012 Express? Don't miss the server name syntax Servername\SQLEXPRESS where you substitute Servername with the name of the computer where the SQL Server 2012 Express installation resides.

-----------------------------------

Trusted Connection
    Driver={SQL Server Native Client 11.0};Server=myServerAddress;
    Database=myDataBase;Trusted_Connection=yes;
------------------------

Connecting to an SQL Server instance
The syntax of specifying the server instance in the value of the server key is the same for all connection strings for SQL Server.
Driver={SQL Server Native Client 11.0};Server=myServerName\theInstanceName;
Database=myDataBase;Trusted_Connection=yes;

------------------
Prompt for username and password
This one is a bit tricky. First you need to set the connection object's Prompt property to adPromptAlways. Then use the connection string to connect to the database.
oConn.Properties("Prompt") = adPromptAlways

oConn.Open "Driver={SQL Server Native Client 11.0};Server=myServerAddress;Database=myDataBase;"
------------------

Enable MARS
    Driver={SQL Server Native Client 11.0};Server=myServerAddress;
    Database=myDataBase;Trusted_Connection=yes;MARS_Connection=yes;
-------------------

Encrypt data sent over network
Driver={SQL Server Native Client 11.0};Server=myServerAddress;
Database=myDataBase;Trusted_Connection=yes;Encrypt=yes;

-------------------

Attach a database file on connect to a local SQL Server Express instance
    Driver={SQL Server Native Client 11.0};Server=.\SQLExpress;
    AttachDbFilename=c:\asd\qwe\mydbfile.mdf;Database=dbname;Trusted_Connection=Yes;

------------------------------
Attach a database file, located in the data directory, on connect to a local SQL Server Express instance
    Driver={SQL Server Native Client 11.0};Server=.\SQLExpress;
    AttachDbFilename=|DataDirectory|mydbfile.mdf;Database=dbname;
    Trusted_Connection=Yes;
-----------------
Database mirroring
If you connect with ADO.NET or the SQL Native Client to a database that is being mirrored, your application can take advantage of the drivers ability to automatically redirect connections when a database mirroring failover occurs. You must specify the initial principal server and database in the connection string and the failover partner server.
    Driver={SQL Server Native Client 11.0};Server=myServerAddress;
     Failover_Partner=myMirrorServerAddress;Database=myDataBase;
    Trusted_Connection=yes;
---------------

Upvotes: 0

Related Questions