SQL Connection & Integrated Authentication

[Merrik]

Has anyone ever tried something like the example below. I'm having problems with it.

expandcollapse popup

$sServer="DBServer"
$db="MYDB"
$sUsername = ""
$sPassword = ""
$SQL_ERROR = "SQL Error"
$SQL_OK = "SQL OK"
Func _SQL_Connect($ADODBHandle, $server, $db, $username, $password, $SQLAuth = True)
    
    $SQLErr = ""
    If $ADODBHandle = -1 Then $ADODBHandle = $SQL_LastConnection
    
    If Not IsObj($ADODBHandle) Then
        $SQLErr = "Invalid ADODB.Connection object, use _SQL_Startup()"
        Return SetError($SQL_ERROR, 0, $SQL_ERROR)
    EndIf
    
    If $SQLAuth = True then 
        $ADODBHandle.Open("DRIVER={SQL Server};SERVER=" & $server & ";DATABASE=" & $db & ";uid=" & $username & ";pwd=" & $password & ";") ;<==Connect with required credentials
    Else
        $ADODBHandle.Properties("Integrated Security").Value = "SSPI"
        $ADODBHandle.Properties("User ID") = $username
        $ADODBHandle.Properties("Password") = $password
        $ADODBHandle.Open("DRIVER={SQL Server};SERVER=" & $server & ";DATABASE=" & $db )
    EndIf
    
    If IsObj( $ADODBHandle) Then
    MsgBox(0, "AutoIT-SQL Result", "We have a connection")
    $adoSQL = "INSERT INTO  dbo.Table VALUES ('" & $comp & "','" & $sTime & "')"
    $ADODBHandle.Execute($adoSQL)
    If @error Then Exit MsgBox(16, "SQL Connect Script", "4: Error occurred. @error = " & @error)

    If Not @error Then
        Return SetError($SQL_OK, 0, $SQL_OK)
    Else
        $SQLErr = "Connection Error"
        Return SetError($SQL_ERROR, 0, $SQL_ERROR)
    EndIf
EndFunc   ;==>_SQL_Connect

[rcmaehl]

What problems are occurring?

[Merrik]

I keep getting errors like undeclared variable "$SQL_LastConnection". I really know little about SQL connections and was hoping someone with a good SQL background could help out. I really need a SQL connection string that uses "integrated authentication".

[Merrik]

I found another example using integrated authentication and I hope someone can help me get one working. I need to an insert into the DB.

Can someone help explain $oProviderGroupID, ;Data Source=datasource, Provider=SQLOLEDB.

I'm guessing I need to change "ADODB.Recordset" to "ADODB.Command"

Local $oRS
Local $oConn
Local $oProviderGroupID

$oConn = ObjCreate ("ADODB.Connection")
$oRS = ObjCreate ("ADODB.Recordset")
$oConn.Open ("Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;User ID=user;Data Source=datasource")
$oProviderGroupID = ObjName ("ProviderGroupID")


$oRS.Open("SELECT DMisProviderGroup.ProviderGroupID FROM DMisProviderGroup WHERE DMisProviderGroup.Active= 'Y' AND LEFT(DMisProviderGroup.ProviderGroupID,3)IN ('LC.','MD.','UA.') AND DMisProviderGroup.ProviderGroupID NOT IN ('LC.PSYCH','LC.PSYKLR','LC.PSYMAD','LC.PSYMID','LC.PSYNEE','LC.PSYOSH')AND SUBSTRING(DMisProviderGroup.ProviderGroupID,4,3) <> 'LAB' ", $oConn, 1, 3)

WinWaitActive ("MIS")


For $iIndex = 1 To $oRS.RecordCount
SEND ($oProviderGroupID)
SEND ("{ENTER}")
$oRS.MoveNext
Next

$oConn.Close
$oConn = 0

[hannes08]

Hello Merrik,

if you're using the _sql UDF, you'll need to start something before you try to connect:

#include <_sql.au3>
Global $oADODB = _SQL_Startup()
 
If $oADODB = $SQL_ERROR Then MsgBox(0 + 16 + 262144, "Error", _SQL_GetErrMsg())
 
 
If _sql_Connect(-1, "server", "db", "", "") = $SQL_ERROR Then
MsgBox(0 + 16 + 262144, "Error", _SQL_GetErrMsg())
_SQL_Close()
Exit
EndIf

[Merrik]

Hi hannes08,

Where do I find _SQL UDF? I need to connect using integrated authentication.

[hannes08]

Hi Merrik,

_sql.udf my ChrisL:

Edited September 29, 2011 by hannes08

[Merrik]

OK I am using the _sql.au3 UDF and am still having problems.

When I connect to a SQL Server in my domain using NT Authority\System, no problem, but I am having problems connecting to a SQL Server in another domain using NT Authority\System.

I can run sqlwb.exe as NT Authority\System and connect to the DB in the other domain and update it, but just not with my Autoit script.

I get this:

"C:\Documents and Settings\LocalService\My Documents\_sql.au3 (220) : ==> The requested action with this object has failed.:

$ADODBHandle.Open("DRIVER={SQL Server};SERVER=" & $server & ";DATABASE=" & $db & ";uid=" & $username & ";pwd=" & $password & ";")

$ADODBHandle.Open("DRIVER={SQL Server};SERVER=" & $server & ";DATABASE=" & $db & ";uid=" & $username & ";pwd=" & $password & ";")^ ERROR

->15:21:52 AutoIT3.exe ended.rc:1

>Exit code: 1 Time: 2.350 "

Then I decided to force Integrated Authentication and I comment out "; $ADODBHandle.Open("DRIVER={SQL Server};SERVER=" & $server & ";DATABASE=" & $db & ";uid=" & $username & ";pwd=" & $password & ";") ;<==Connect with required credentials

; Else"

Then I get"

>Running:(3.3.6.1):C:\Program Files\AutoIt3\autoit3.exe "C:\Documents and Settings\LocalService\My Documents\test_Today.au3"

C:\Documents and Settings\LocalService\My Documents\_sql.au3 (222) : ==> The requested action with this object has failed.:

$ADODBHandle.Properties("Integrated Security").Value = "SSPI"

$ADODBHandle.Properties("Integrated Security")^ ERROR

#include <_sql.au3>
Global $oADODB = _SQL_Startup()
$comp = "MyPC"

If $oADODB = $SQL_ERROR Then MsgBox(0 + 16 + 262144, "Error", _SQL_GetErrMsg())

;If _sql_Connect(-1, "DBSERVER\ins1", "PCDB", "", "") = $SQL_ERROR Then
MsgBox(0 + 16 + 262144, "Error", _SQL_GetErrMsg())
Else
    MsgBox(0,"","You connected!")
    If _SQL_Execute(-1,"INSERT INTO dbo.Computers (Computer_Name) VALUES ('"& $comp & "');") Then

    MsgBox(0,"","Inserted")
    EndIf
_SQL_Close()
Exit
EndIf

Any help is appreciated.

[Merrik]

Solution:

All that was missing was the fully qualified name of my DB Server

If _sql_Connect(-1, "DBSERVER.mydomain.us\ins1", "PCDB", "", "") = $SQL_ERROR Then