sql server connection

[dinomali]

Hello everyone!

This is my first topic here. Before I had registered here I was looking an answer for my problem couple of days. I hope you  I will manage to explain my problem.

I wrote a script where I'm connecting my autoit script to sql server. My connection is succesfull when I' m logged on windows with my username and password. Now I'm trying to  write a script which can connect to sql server with my sql server username and password and a script is run under  a different windows account. I hope that you understood me, if not, please ask me questions. 

I'm not usin sql.au3, I will if is nessesary.

expandcollapse popup

#include <Array.au3>

$oMyError = ObjEvent("AutoIt.Error","MyErrFunc") 
local $result
local $conn = ObjCreate( "ADODB.Connection" )
$sQuery = "select * from djelatnost where id=4"
$conn.open("DRIVER={SQL Server};SERVER=sqlservername;DATABASE=databasename;UID=;PWD=;")
$result = $conn.Execute($sQuery)

local $sMsg = ""

; ... $result contains result set

For $oField In $result.Fields
    $sMsg &= $oField.Name & @TAB
Next
$sMsg &= @CRLF

While Not $result.EOF
    For $oField in $result.Fields
        $sMsg &= $oField.Value & @TAB
    next
    $sMsg &= @CRLF
    $result.MoveNext
wend
 
$result.Close
 
ConsoleWrite($sMsg)


$conn.Close ; ==> Close the database



Func MyErrFunc()
  Msgbox(0,"AutoItCOM Test","We intercepted a COM Error !"    & @CRLF  & @CRLF & _
             "err.description is: " & @TAB & $oMyError.description  & @CRLF & _
             "err.windescription:"   & @TAB & $oMyError.windescription & @CRLF & _
             "err.number is: "       & @TAB & hex($oMyError.number,8)  & @CRLF & _
             "err.lastdllerror is: "   & @TAB & $oMyError.lastdllerror   & @CRLF & _
             "err.scriptline is: "   & @TAB & $oMyError.scriptline   & @CRLF & _
             "err.source is: "       & @TAB & $oMyError.source       & @CRLF & _
             "err.helpfile is: "       & @TAB & $oMyError.helpfile     & @CRLF & _
             "err.helpcontext is: " & @TAB & $oMyError.helpcontext _
            )
Endfunc

When I write my username and password, doesn't help. Is there any parametar wich I have to write in $conn.open ?

Please help me, thank you

[mLipok]

Your scrit is good. = works well.

possible causes of the problems:

• wrong username or password
• lack of permissions on the specified login name
• wrongspecified location, an instance of SQL Server
• incorrect configuration of SQL Server
• incorrect firewall settings

 

EDIT:

btw.

Welcome to the forum

Edited March 20, 2014 by mLipok

[dinomali]

mLipok, thank you for your reply. I think the problem might be with Intergrated security. When I'm logged on my computer with my windows username and password (this username and passw I also use for connecting to company sql server) to run a script username and passw doesn't need to be written in script - it uses integrated security.

But when I'm logged with a different username and passw( witch doesnt have a permission for sql server) the script has to have my username and password

when I write in

$conn.open("DRIVER={SQL Server};SERVER=sqlservername;DATABASE=databasename;UID=username;PWD=password;")

I get com error - that username and password are incorrect - they aren't

I also tried with 'domainnameusername'

For example I'm using openrowset in Report server, in openrowset I don't write my username and passw. They are written in datasources property for specific report in Report server, and the box   "Use as Windows credentials when connecting to the data source" is marked.

So users of Report server logged with their username and passw  can see report through my username and passw

Is there any way I can do this with Autoit, any additional parametar?

[mLipok]

you can not put your windows login and password here: UID=username;PWD=password

When you use Windows Authentification for example:

$ADODBHandle.Properties("Integrated Security").Value = "SSPI"
$ADODBHandle.Properties("User ID") = $username
$ADODBHandle.Properties("Password") = $password
$sConnectionString = "DRIVER={SQL Server};SERVER=" & $server & ";DATABASE=" & $db

Then you must be loged to windows with login and password which are accepted by SQL

When you use SQL Authentification for example:

$conn.open("DRIVER={SQL Server};SERVER=sqlservername;DATABASE=databasename;UID=;PWD=;")

Then you can not use Windows login and password but only SQL login and password for example sa user or other which must have appropriate privileges granted by / on SQL server.

Edited March 21, 2014 by mLipok

[ternal]

On 3/21/2014 at 4:35 PM, mLipok said:

you can not put your windows login and password here: UID=username;PWD=password

When you use Windows Authentification for example:

$ADODBHandle.Properties("Integrated Security").Value = "SSPI"
$ADODBHandle.Properties("User ID") = $username
$ADODBHandle.Properties("Password") = $password
$sConnectionString = "DRIVER={SQL Server};SERVER=" & $server & ";DATABASE=" & $db

Then you must be loged to windows with login and password which are accepted by SQL

When you use SQL Authentification for example:

$conn.open("DRIVER={SQL Server};SERVER=sqlservername;DATABASE=databasename;UID=;PWD=;")

Then you can not use Windows login and password but only SQL login and password for example sa user or other which must have appropriate privileges granted by / on SQL server.

 

In ssms you  can  add windows  credentials  to the windows  credentials manager. 

Like this  you  can acces the server from a different  domain. 

Would this work for autoit  as well?  I am new to sql and kerberos so sorry  for any stupid questions  if I asked any.