Connect to MS SQL

[UKmedia]

Hey Guys,

I have a problem with connecting to a MS SQL database through a script. It works prefect on my login as I am a domain admin bu on normal users it fails to connect with the error:

Login failed for user 'DomainName\Username'

I have posted the code for you to review but I am passing the server, database, username, password to use already through the connection string.

'Connect to SQL and store the results in the table

Const adOpenStatic = 3
Const adLockOptimistic = 3

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open _
    "Provider=SQLOLEDB;Data Source=ULTRATECIDB;Trusted_Connection=Yes;Initial Catalog=openfire;UserID=username;Password=password;"

objRecordSet.Open "INSERT INTO jiveLoginStatus(Jdate,Jtime,Jip,Jcomputer,Jusername,Jstatus) values('" & Date() & "','" & Time() & "','" & IP_ADDRESS & "','" & strUsername & "','" & strComputer & "','" & strStatus & "')", _
        objConnection, adOpenStatic, adLockOptimistic

UKmedia productions

http://www.uk-media.com

 

[neualex]

>Login failed for user 'DomainName\Username'

This error is pretty straightforward, the username is not properly authorized to the "openfire" DB.

At least, give it DataReader permission to be able to pull the data.

...neualex

 

[UKmedia]

If you look at the script I am passing a username/password in the connection string that has full dbowner rights. I do not want to set everyone on my domain to the database otherwise I will have to setuop everyone 1 by 1 in the security user options on SQL.

(ok, I have placed username and password for the login but the login is a SQL user that has dbowner rights set. So it should use that and not the local username of the PC that is running the script.

UKmedia productions

http://www.uk-media.com

 

[neualex]

That means, you are able to login to the SQL console with that username/password with no trouble, if so try this connection string instead:

connectionString = "Provider=SQLOLEDB.1;Password=myPass;Persist Security Info=True;User ID=myUSER;Initial Catalog=myDB;Data Source=myServer"

...neualex

 

[UKmedia]

nice one that worked for me

UKmedia productions

http://www.uk-media.com