none
Error when re-starting an SQL Server Instance from VBA - [SQL-DMO]Service Control Error: Access is denied.

    Question

  • Our Application needs to have the use of the [Named Pipes] and [TCP/IP] Protocols.  To save our Users the hassle of having to enable them manually our application has always done it for them and then stopped and restarted SQL Server.  However since the advent of Vista and Windows 7, the restart stopped working.  We solved the problem by detecting the failure to re-start and giving Users sufficient instructions for them to do it themselves.  Since this only occurs during installation on the Server, this approach has not been too much of a hassle.  However we would like to tidy this up if possible. This the VBA code that is causing the problem:

        obj_SQLServerInstance2.Start _
            g_SQL_UseWindowsAuthentication, _
            SQLServerInstance, _
            UserName, _
            Password

    This still works when run under XP, but fails under Windows 7 with the Error:

    [SQL-DMO]Service Control Error: Access is denied.

    Does anyone know how to resolve the problem?

    Friday, July 25, 2014 8:06 AM

Answers

All replies

  • This still works when run under XP, but fails under Windows 7 with the Error:

    [SQL-DMO]Service Control Error: Access is denied.

    Hello Brian,

    Under Windows 7 you have to run the VBA script with admin permission, a normal user don't have permissions to start/stop services.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, July 25, 2014 8:13 AM
  • Hi Olaf,

    Yes you were right.  If I ran MSACCESS as THE Administrator and then opened our ADP Access Project file  SQLServerInstance2 was restarted OK.  I am AN Administrator an it did not work for me previously.

    So the issue now becomes- how do I programmatically elevate the privileges of the VBA so that it will temporarily give users sufficient rights to restart  SQLServerInstance2?  Or do we need to use a command line switch to always run MSACCESS as THE Administrator?

    Tuesday, July 29, 2014 1:34 AM