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!
How do I connect to SQL from VB.Net
In VB6 I was able to use:
Now I'm trying to move into the 21st century and use .NET with classes etc.
I have tried many variations on the following:
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.
Set dbData = New ADODB.Connection
sProvider = "SQLOLEDB.1"
dbData.ConnectionString = "Provider=" & sProvider & ";Integrated Security=SSPI;" _
& "Initial Catalog=" & sCatalog & ";Data Source=" & sServer
dbdata.Open()
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()
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.
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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;Da
Create a SQL user on your instance and give it the appropriate permissions for the application then use those credentials.
ConnectionString = "Server=myServerAddress;Da
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/
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/
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!
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!
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!
"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.
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
Invalid value for key 'integrated security'.
The error happens on the connection string - before attempting to open the database.
What am I doing wrong?
here is my connect statement
dbData = New SqlConnection
dbData.ConnectionString = "Data Source= " & sServer & "; Initial Catalog = " & sCatalog & "; Integrated Security = sspi)"
dbData.Open()
Invalid value for key 'integrated security'.
The error happens on the connection string - before attempting to open the database.
What am I doing wrong?
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.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial