Best Practices - SQL 2000 Question 1

[clicker666]

I'm doing some VB work connecting to my SQL 2000 backend, which also is my Great Plains SQL server. When coding I've noticed I have to use UID and PWD in my code. I hate hard coding, or even having to store these variables in the registry or an ini file.

My SQL server is set up for SQL Server authentication, not Windows Integrated Security. I would imagine that using Windows security I would eliminate having to code the UID and PWD into my app. Will GP run off this security method? Is there a preferred method when setting up a SQL server that will be running GP for security?

It seems to me that having the system dependant on passwords that need to be manually changed in a number of places is a bad practice. Any input?

 

[winthropdc]

Please look up RetrieveGlobals.dll on PartnerSource or CustomerSource. This will handle the ability of VBA to create an ADO connection without hard coding passwords into the code.

Dim userinfo As New RetrieveGlobals.retrieveuserinfo
Dim luserid As String
Dim lsqlpassword As String
Dim lintercompanyid As String
Dim lsqldatasourcename As String

Dim cn As New ADODB.Connection
Dim constring As String

' RetrieveGlobals
lsqldatasourcename = userinfo.sql_datasourcename()
luserid = userinfo.retrieve_user()
lsqlpassword = userinfo.sql_password()
lintercompanyid = userinfo.intercompany_id()
'MsgBox (luserid & " " & lsqlpassword & " " & lintercompanyid & " " & lsqldatasourcename)

constring = "Provider=MSDASQL" & _
";Data Source=" & lsqldatasourcename & _
";User ID=" & luserid & _
";Password=" & lsqlpassword & _
";Initial Catalog=" & lintercompanyid
'MsgBox constring

' ADO Connection
With cn
.ConnectionString = constring
.CursorLocation = adUseClient
.Open
End With


David Musgrave [MSFT]
Senior Development Consultant
MBS Services - Asia Pacific

Microsoft Business Solutions

http://www.microsoft.com/BusinessSolutions

Any views contained within are my personal views and
not necessarily Microsoft Business Solutions policy.
This posting is provided "AS IS" with no warranties,
and confers no rights.

 

[clicker666]

Thanks!

 

[clicker666]

That appears only to work when you are logged onto GP and connected to a specific company. Would I be correct in assuming this? Is there ANY way to get the OBDC connection info out of the control panel and into my application, without running GP?

 

[winthropdc]

You are correct, this code and dll is how you can create an ADO connection to access tables from inside a Great Plains VBA session.

You could try reading the Dex.ini file for the settings..

SQLLastDataSource=
SQLLastUser=

I know of no way to identify what password to use.

David Musgrave [MSFT]
Senior Development Consultant
MBS Services - Asia Pacific

Microsoft Business Solutions

http://www.microsoft.com/BusinessSolutions

Any views contained within are my personal views and
not necessarily Microsoft Business Solutions policy.
This posting is provided "AS IS" with no warranties,
and confers no rights.

 

[clicker666]

Thanks. I'll post over on VB, I imagine this has gone out of the GP realms.