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.
TopCatOnyxAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
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"
aranaCommented:
integrated security SSPI is the prefered method since TRUE is not supported in all versions of sql providers while sspi IS
aranaCommented:
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/
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

TopCatOnyxAuthor 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!
TopCatOnyxAuthor 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!
aranaCommented:
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.
TopCatOnyxAuthor Commented:
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?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TopCatOnyxAuthor 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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.