no permission createobject

Question

question

TarekHalloun-9204 asked May 3, '22 TarekHalloun-9204 answered May 6, '22

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 error

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

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

Yufeishao-msft · May 06 at 01:51 AM

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 ·

Answer 1 · 2022-05-03T08:39:16.377Z

OlafHelper-2800 answered May 3, '22 TarekHalloun-9204 commented May 3, '22

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

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

TarekHalloun-9204 · May 03 at 08:58 AM

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

0 ·

Answer 2 · 2022-05-03T14:36:08.407Z

TomPhillips-1744 answered May 3, '22 TarekHalloun-9204 commented May 3, '22

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

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

TarekHalloun-9204 · May 03 at 02:50 PM

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

0 ·

Answer 3 · 2022-05-03T21:57:01.977Z

ErlandSommarskog answered May 3, '22

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

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

Answer 4 · 2022-05-04T06:57:37.167Z

Yufeishao-msft answered May 4, '22

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

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

Answer 5 · 2022-05-06T07:40:23.923Z

TarekHalloun-9204 answered May 6, '22

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

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

question