-
Oct 13th, 2020, 10:29 AM
#1
Thread Starter
Fanatic Member
Connection string for Integrated Security
Hello:
I am trying to connection using old school VBA.
This works in VBA!
Code:
connectionString = "Provider=sqloledb;Data Source=w2012;Initial Catalog=M1_SU;User Id=m1view;Password=connect2m1;"
This works in VB.NET, but says the server does not exist or access is denied...
Code:
"Provider=sqloledb;Data Source=sage;Initial Catalog=JobSheet;persist security info=True;Integrated Security=SSPI;"
Why would the server be available in VB.NET but not in VBA?
Here's the complete code. I must be missing something..
Code:
Sub GetJobs_Click()
Dim connectionString As String
' connectionString = "Provider=sqloledb;Data Source=w2012;Initial Catalog=M1_SU;User Id=m1view;Password=connect2m1;"
connectionString = "Provider=sqloledb;Data Source=sage;Initial Catalog=JobSheet;persist security info=True;Integrated Security=SSPI;"
Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Dim sql As String
sql = "SELECT * From [JobSheet].[dbo].[JobInfo] ORDER BY JobNo "
MsgBox (sql)
cn.connectionString = connectionString
cn.Open ' ERROR'S HERE
rs.Open sql, cn, adOpenKeyset, adLockReadOnly, adCmdText
rs.MoveFirst
Do While Not rs.EOF
MsgBox (rs.Fields("JobNo"))
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub
Thanks!
Last edited by ssabc; Oct 13th, 2020 at 11:44 AM.
- A 'Hyperactive Member' trying to make a difference in a hyperactive world! And recently, I've been promoted to a 'Finatic Member,' whatever that means!
-
Oct 13th, 2020, 04:09 PM
#2
Re: Connection string for Integrated Security
You have different Data Source names in those two connections strings - do both of those servers exist?
Also when you are using integrated security then the person running the application would need a user account and appropriate permissions in the target server / database.
-
Oct 14th, 2020, 08:38 AM
#3
Thread Starter
Fanatic Member
Re: Connection string for Integrated Security
The first cn is remmed out. It is an example showing what works on a completely different dataset.
The second cn uses Integrated Security, is the one I need, and is not working.
- A 'Hyperactive Member' trying to make a difference in a hyperactive world! And recently, I've been promoted to a 'Finatic Member,' whatever that means!
-
Oct 14th, 2020, 08:42 AM
#4
Thread Starter
Fanatic Member
Re: Connection string for Integrated Security
Oh yes, we have permissions in the Db. As I mentioned, the Integrated Security string works in VB.NET but not in VBA.
- A 'Hyperactive Member' trying to make a difference in a hyperactive world! And recently, I've been promoted to a 'Finatic Member,' whatever that means!
-
Oct 14th, 2020, 09:34 AM
#5
Hyperactive Member
Re: Connection string for Integrated Security
And if so?
Code:
Public conn As New ADODB.Connection 'Variables for working with the database
Public rs As New ADODB.Recordset
'...................
Set conn = New ADODB.Connection
conn ="Provider=sqloledb;Data Source=sage;Initial Catalog=JobSheet;persist security info=True;Integrated Security=SSPI;"
If conn.State <> 1 Then
MsgBox "Database connection not established!", vbOKOnly + vbCritical, "Error!"
Exit Sub
End If
-
Oct 14th, 2020, 09:57 AM
#6
Thread Starter
Fanatic Member
Re: Connection string for Integrated Security
cn=0. That's not a surprise.
Am I missing references? The think that kills me is that this works:
Code:
connectionString = "Provider=sqloledb;Data Source=w2012;Initial Catalog=M1_SU;User Id=m1view;Password=connect2m1;"
Of course I don't want this, I want this:
Code:
connectionString = "Provider=sqloledb;Data Source=sage;Initial Catalog=JobSheet;persist security info=True;Integrated Security=SSPI;"
Here's the database image for reference. There's got to be something wrong with the syntax. Though the identical syntax works fine in VB.NET 2017 with the same database permissions and user login.
Attachment 179008
- A 'Hyperactive Member' trying to make a difference in a hyperactive world! And recently, I've been promoted to a 'Finatic Member,' whatever that means!
-
Oct 14th, 2020, 02:16 PM
#7
Re: Connection string for Integrated Security
Those two have different sources as well, have you tried using the same data source in both strings to see if you get different results?
-
Oct 16th, 2020, 10:48 AM
#8
Thread Starter
Fanatic Member
Re: Connection string for Integrated Security
The solution is!!:
Code:
connectionString = "Provider=sqloledb;Data Source=sage/SQLEXPRESS;Initial Catalog=JobSheet;persist security info=True;Integrated Security=SSPI;"
Because the other server has two instances of SQL Server
- A 'Hyperactive Member' trying to make a difference in a hyperactive world! And recently, I've been promoted to a 'Finatic Member,' whatever that means!
-
Oct 16th, 2020, 12:05 PM
#9
Re: Connection string for Integrated Security
Use backslash for SQL instance name, e.g. SAGE\SQLEXPRESS
cheers,
</wqw>
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|