Connecting to SQL Server with Windows User not working

[steff]

Hello all

I wrote a script which uses _sql.au3 udf to connect to a SQL Server 2000.

I run the script on the server which hosts the SQL Server DB.

I'm able to connect using a DB user, such as sa, but not with Windows users, i.e. Administrator.

I tried with a simple "administrator" and then in the form "domain\administrator", but neither works. The error I have is "Connection error".

I'm able to connect in both ways with Sql Server Management Studio, being connected on the server as Administrator.

Here's what I did (I just copied some examples found in the forum):

Local $dbServer=server
Local $dbName=name
Local $dbUser=user
Local $dbPasswd

$oADODB = _SQL_Startup()
If $oADODB = $SQL_ERROR then Msgbox(0 + 16 +262144,$msgboxHd & $msgboxErr,_SQL_GetErrMsg())
_SQL_RegisterErrorHandler();register the error handler to prevent hard crash on COM error

If _SQL_Connect(-1,$dbServer,$dbName,$dbUser,$dbPasswd) = $SQL_ERROR then 
    Msgbox(0 + 16 +262144,$msgboxHd & $msgboxErr,_SQL_GetErrMsg())
    Exit
EndIf

... some stuff ...

Any hint?

Thanks in advance for your help

stefano

Edited January 18, 2011 by steff

[ReFran]

Hi,

under Administration ODBC you can set up a user-DSN and test the correct settings and connection.

If that works you can use:

func Sql_connect()

$oDb.Open($dbConnectionName) ;ODBC-DSN defined Database Connection

If IsObj($oDb) = 0 Then

MsgBox(0,"","Not connected")

Exit

endif

endfunc

HTH, Reinhard

[steff]

Hi,

under Administration ODBC you can set up a user-DSN and test the correct settings and connection.

If that works you can use:

func Sql_connect()

$oDb.Open($dbConnectionName) ;ODBC-DSN defined Database Connection

If IsObj($oDb) = 0 Then

MsgBox(0,"","Not connected")

Exit

endif

endfunc

HTH, Reinhard

Thanks Reinhard for your answer.

I setup a system DSN called SQL-SERVER-LOCALE and I got an error running the script this way:

Sql_connect()

func Sql_connect()
$oDb.Open("SQL-SERVER-LOCALE") ;ODBC-DSN defined Database Connection
If IsObj($oDb) = 0 Then
MsgBox(0,"","Not connected")
Exit
endif
endfunc

Here's the error:

>"C:\Program Files\AutoIt3\SciTE\..\autoit3.exe" /ErrorStdOut "X:\0.2\test_db.au3"    
X:\0.2\test_db.au3 (4) : ==> Expected a "=" operator in assignment statement.:
$oDb.Open("SQL-SERVER-LOCALE")
$oDb^ ERROR
>Exit code: 1    Time: 0.622

Sorry for my naivity: shouldn't I create the object first?

I mean, put a $oDb=ObjCreate($something) at the beginning...

Thanks

[ChrisL]

I'm not really sure about the Windows Authentication.

Maybe try this instead of the function in _SQL.au3 make sure when you call the function to add False on as the last parameter _SQL_Connect(-1,$dbServer,$dbName,$dbUser,$dbPasswd, False)

I can't actually try this, I'm just guessing from Googling

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 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

[ReFran]

....Sorry for my naivity: shouldn't I create the object first?

....I mean, put a $oDb=ObjCreate($something) at the beginning...

Sure, I forgot that:

DIM $oDB

$oDb = ObjCreate("ADODB.Connection")

One important question: Did you test the connection manual?

Go to ODBC -> choose your DSN:"SQL-SERVER-LOCALE" ->configure -> ready(left button) -> test connection (left button)

Only if that works you can concentrate on scripting.

best regards, Reinhard