asked on

How do I connect to SQL from VB.Net

In VB6 I was able to use:

Set dbData = New ADODB.Connection
sProvider = "SQLOLEDB.1"
dbData.ConnectionString = "Provider=" & sProvider & ";Integrated Security=SSPI;" _
                   & "Initial Catalog=" & sCatalog & ";Data Source=" & sServer
dbdata.Open()

Open in new window

and everyone was happy. The connection worked and I was able to access all my data.
Now I'm trying to move into the 21st century and use .NET with classes etc.
I have tried many variations on the following:

        dbData = New SqlConnection
        dbData.ConnectionString = "Data Source= " & sServer & "; Initial Catalog = " & sCatalog & ";  Trusted_Connection=True)"
        dbData.Open()

Open in new window

I have tried to set "Integrated Security = True" or "Yes" and I get an error: Invalid value for key 'integrated security'.
If I leave off the Trusted_Connection, I get an error:{"Login failed for user ''."}
The only one that works is to use Uid and Pwd set to my windows logon user name and password. However I need to have this work without usernames or passwords.
Anybody have any ideas about this?
By the way, the VB6 program runs fine from the same computer on which I'm doing my development - so I know that the SQL server is accessible. SQL is setup for Windows Logon.

SOLUTION

Brian Crowe

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Arana (G.P.)

integrated security SSPI is the prefered method since TRUE is not supported in all versions of sql providers while sspi IS

Arana (G.P.)

since you are using SQLOLEDB look here
http://www.connectionstrings.com/microsoft-ole-db-provider-for-sql-server-sqloledb/

or you can try this other that looks more like the one you are trying
http://www.connectionstrings.com/sqlconnection/

Richard

ASKER

Hi Brian,
I set up a user in SQL with SQL authentication. When I try to log into the Management Studio using the new user name, to test the logon, I get an error:
TITLE:
Connect to Server
------------------------------
Cannot connect to ONYX.
------------------------------
ADDITIONAL INFORMATION:
A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233)

And if I try the new user name and password in the program I get an SQL error: Login failed for user 'SQLUser'.
My user name is SQLUser and the password is sql&user.
The above are (obviously) only for testing. If I can get it to work, they will become something realistic!

Richard

ASKER

Hi Arana,
"since you are using SQLOLEDB look here"
I'm not sure what I'm using in the VB.Net version - I tried to set a provider and nothing worked.
Also, I tried setting the Integrated Security:
...;Integrated Security = sspi)
and got this error:
Invalid value for key 'integrated security'.

This is becoming quite frustrating!

SOLUTION

Arana (G.P.)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Richard

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Richard

ASKER

After playing around I found that if I set up an SQL user (SQLUser) and if I set the Integrated Security to sspi (or SSPI) that I can log onto the database with no errors. I still don't understand why, but for now this works. Each of you contributed to the solution - hence the split. Thank you both for your prompt replies.