question

TarekHalloun-9204 avatar image
0 Votes"
TarekHalloun-9204 asked TarekHalloun-9204 answered

no permission createobject

hello everyone

I have a script on sql that uses a vbscript to create an excel file

It was working well on a server 2008R2 and sql 2008r2 with excel 2007 but now we upgraded to server 2019 and sql 2019 with excel 2016 but the script broke

If i run the script in CMD , it works well

If i run if from sql i get no permission create object , meaning i cannot create the excel

I tried with dcom but nothing worked ( added everyone to the excel application )
i checked the logon users shown here and added the sql agent and server to the folder permissions but still same error198481-sql1.png198368-sql2.png198369-sql3.png

what can i try ?


sql-server-general
sql1.png (18.4 KiB)
sql2.png (18.5 KiB)
sql3.png (63.5 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Could you please validate all the answers so far and provide any update?
Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Thank you for understanding!

0 Votes 0 ·
OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered TarekHalloun-9204 commented

In current SQL Server version xp_cmdshell is disabled, for safety reasons. You have to enable it first.
Using xp_cmdshell together with VB script is really not a good idea.

See https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/xp-cmdshell-transact-sql?view=sql-server-ver15

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

xpcmdshellenabled is true in the server security facet - so it is not disabled on my server

0 Votes 0 ·
TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered TarekHalloun-9204 commented

Check the effective rights on the "ExcelFormatting" directory for MSSQLSERVER.

Does your script run, if you run it manually from the SQL Server, without SSMS?

That error can also mean VB cannot find a library used by the script.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

hello tom , can you explain more about the effective rights ?

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

Maybe that is a good thing. Is the VBscript using the COM objects for Excel? Excel can open windows. You cannot open windows from a service, since it does not have a desktop. You may been using this for many years, but to me, this sounds like an accident waiting to happen.

So I think you should reconsider this design altogether.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Yufeishao-msft avatar image
0 Votes"
Yufeishao-msft answered

HI @TarekHalloun-9204,

What the user do you use to log in SSMS?
This could also be a issue with Windows permissions. You might consider switching your file to other location, Windows does not like your file in plain C:\ dierctly. Make sure your antivirus or a company security policy are not organized.




If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.




5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TarekHalloun-9204 avatar image
0 Votes"
TarekHalloun-9204 answered

i ended up giving permission to the office16 folder and the DCOM excel app for the mssql account
worked like a charm

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.