How do I connect to SQL from VB.Net

TopCatOnyx
TopCatOnyx used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Brian CroweDatabase Administrator
Top Expert 2005
Commented:
Unless you are going to use the windows credentials of your user then you will likely want to use SQL Authentication instead of Windows Authentication.

Create a SQL user on your instance and give it the appropriate permissions for the application then use those credentials.

ConnectionString = "Server=myServerAddress;Database=myDataBase;User Id=myUsername; Password=myPassword"
integrated security SSPI is the prefered method since TRUE is not supported in all versions of sql providers while sspi IS
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/
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
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!

Author

Commented:
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!
according to MS
When false, User ID and Password are specified in the connection. When true, the current Windows account credentials are used for authentication.
Recognized values are true, false, yes, no, and sspi (strongly recommended), which is equivalent to true.
Hi Arana,
here is my connect statement
        dbData = New SqlConnection
        dbData.ConnectionString = "Data Source= " & sServer & "; Initial Catalog = " & sCatalog & "; Integrated Security = sspi)"
        dbData.Open()

Open in new window

and here is the error I get:
 Invalid value for key 'integrated security'.
The error happens on the connection string - before attempting to open the database.

What am I doing wrong?

Author

Commented:
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial