April 26, 2021 at 1:55 pm
Hello~
I am a developer of a DB Application (Azure SQL Server Back End/MS Access Front End), where we have recently encrypted several fields.
We have a scheduled SQL Server Job, which has started hanging -- this job simply executes a VBS Script from the command line using 'cscript'. This VBS Script connects to the DB and inserts data into a specific table, which has our encrypted fields. This job is run using a proxy which is set up with my login.
The VBS Script executes without issue when I call it from the command line, however when called from the SQL Server Job, it seems to get hung up on the connection string
'Connect to SQL
Set objConn = CreateObject("ADODB.Connection")
strConn = strConn & "DRIVER=ODBC Driver 17 for SQL Server"
strConn = strConn & ";SERVER=MYSERVER"
strConn = strConn & ";DATABASE=MYDB"
strConn = strConn & ";TrustServerCertificate=Yes"
strConn = strConn & ";Trusted_Connection=Yes"
strConn = strConn & ";ColumnEncryption=Enabled"
objConn.Open strConn
No error is produced -- the job simply hangs at this point -- indefinitely. If I change the connection string to a non-encrypted string, the job will execute past this point, but then the script of course will not work properly. Again, this script executes without issue when run directly or called from the command line.
Perhaps there is some setting in SSMS that would solve this? Or perhaps this simply isn't possible to execute from a SQL Server Job?
April 27, 2021 at 2:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
April 27, 2021 at 5:42 pm
Just to confirm, if you log into the server while the job is running and load up task manager, who does cscript.exe say it is running as?
The reason I ask is I am thinking that MAYBE the proxy account or job is configured incorrectly resulting in cscript running as someone other than you.
If it is running as you, I would then check on the server (exec sp_who2) to see if the script is connected or not (extended events/profiler can help with this too). It MIGHT be that the script is actually progressing past the objConn.Open statement but hanging somewhere else.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
May 7, 2021 at 6:12 am
This was removed by the editor as SPAM
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply