Sign in to follow this  
Followers 0
steff

Connecting to SQL Server with Windows User not working

5 posts in this topic

#1 ·  Posted (edited)

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

Share this post


Link to post
Share on other sites



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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0