invalid authorization specification sql server 2008R2 in vb6

Jitendra Shekhawat

9y4.1kVisual Basic .NET

Private Sub Command_Click()

Dim MyConnObj As New ADODB.Connection 'ADODB Connection Object

Dim myRecSet As New ADODB.Recordset 'Recordset Object

Dim sqlStr As String ' String variable to store sql command

MyConnObj.Open "Provider = sqloledb;" & "Data Source=JITENDRA-PC\SQLEXPRESS;" & "Initial Catalog=Test;"

sqlStr = "select * from Test"

myRecSet.Open sqlStr, MyConnObj, adOpenKeyset

MsgBox "Total Number of records = " & myRecSet.RecordCount

MyConnObj.Close

End Sub

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nitin Sontakke9y ago
Accepted answer
It will either be this if you are using sql server authentication:

Provider=sqloledb;Data Source=JITENDRA-PC\SQLEXPRESS;Initial Catalog=Test;
User Id=myUsername;Password=myPassword;

Or will be this if you are using windows authentication:

Provider=sqloledb;Data Source=JITENDRA-PC\SQLEXPRESS;Initial Catalog=Test;
Integrated Security=SSPI;
Jitendra Shekhawat9y ago
Thanks sir,
Problem Solved.
Nitin Sontakke9y ago
I am not seeing any change in the connection string. Compare yours with one I have given.
Jitendra Shekhawat9y ago
unable to connect database
Error show "-
invalid authorization specification

Dim MyConnObj As New ADODB.Connection 'ADODB Connection Object
Dim myRecSet As New ADODB.Recordset 'Recordset Object
Dim sqlStr As String ' String variable to store sql command
con.Open "Provider = sqloledb;" & "Data Source=JITENDRA-PC\SQLEXPRESS;" & "Initial Catalog=Test;"
sqlStr = "select * from Test"
myRecSet.Open sqlStr, MyConnObj, adOpenKeyset
MsgBox "Total Number of records = " & myRecSet.RecordCount
MyConnObj.Close
End Sub
Nitin Sontakke9y ago
Not sure what you changed...

Are you able to connect to your database by any other means? SQL Server Management Studio. Please answer this question. It is very important.

If yes, all you have do is modify the connection string as appropriate.
Jitendra Shekhawat9y ago
Change do this..
Bur Error same....:(
Nitin Sontakke9y ago
First, I always find it hugely amusing that developers needlessly concatenate the string. For example why following

MyConnObj.Open "Provider = sqloledb;" & "Data Source=JITENDRA-PC\SQLEXPRESS;" & "Initial Catalog=Test;"

cannot be written simply as

MyConnObj.Open "Provider=sqloledb;Data Source=JITENDRA-PC\SQLEXPRESS;Initial Catalog=Test;"

And to answer your question, you haven't mention any authentication information in connection string. Is it windows authentication or sql server authentication?

How are you connecting otherwise using Management Studio?

See connectionstrings.com for the details