October 29, 2015 at 5:48 am
Hi all,
I have a permissions issue that I am hoping you can help me with. I have a Windows NT group that is used to delegate certain database responsibilities to other members of staff and I am trying to grant permissions for the members of the group to be be able to establish database mirroring sessions, as in run the following:
ALTER DATABASE <database>
SET PARTNER = 'tcp://principal_server.domain.com:port';
Although the group has db_owner role membership to the user database which grants the ALTER permission on the database, the following is being generated in the error log when they get to this step on the intended Mirror instance after restoring the database correctly in preperation:
SqlDumpExceptionHandler: Process 59 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
* *******************************************************************************
*
* BEGIN STACK DUMP:
* 10/29/15 11:16:15 spid 59
*
*
* Exception Address = 00007FF9A6AF838C Module(sqlmin+000000000003838C)
* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
* Access Violation occurred reading address 00000000000000D8
* Input Buffer 210 bytes -
* alter database <redacted> set partner = '<redacted>';
As you can see, the statement is denied to the user. There are no issues with the database as I am able to run the same query successfully using my own sysadmin account after the failed attempt.
Is anyone able to provide information on what other minimum permissions the group might need to successfully enable them to setup a mirroring session?
Many thanks in advance,
Ben
October 29, 2015 at 5:58 am
From BOL
Requires ALTER permission on the database and CREATE ENDPOINT permission, or membership in the sysadmin fixed server role.
October 29, 2015 at 6:07 am
Thanks for the response, however please correct me if I'm wrong but the CREATE ENDPOINT permission is in relation to creating the mirroring endpoint that mirroring sessions will use, not establishing the mirroring session itself which is a separate article.. The endpoints in my scenario are already configured and started.
October 29, 2015 at 6:20 am
Maybe they are missing the connect on endpoint permission then if the endpoint is already connected
October 29, 2015 at 8:52 am
stobe (10/29/2015)
Hi all,I have a permissions issue that I am hoping you can help me with. I have a Windows NT group that is used to delegate certain database responsibilities to other members of staff and I am trying to grant permissions for the members of the group to be be able to establish database mirroring sessions, as in run the following:
ALTER DATABASE <database>
SET PARTNER = 'tcp://principal_server.domain.com:port';
Although the group has db_owner role membership to the user database which grants the ALTER permission on the database, the following is being generated in the error log when they get to this step on the intended Mirror instance after restoring the database correctly in preperation:
SqlDumpExceptionHandler: Process 59 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
* *******************************************************************************
*
* BEGIN STACK DUMP:
* 10/29/15 11:16:15 spid 59
*
*
* Exception Address = 00007FF9A6AF838C Module(sqlmin+000000000003838C)
* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
* Access Violation occurred reading address 00000000000000D8
* Input Buffer 210 bytes -
* alter database <redacted> set partner = '<redacted>';
As you can see, the statement is denied to the user. There are no issues with the database as I am able to run the same query successfully using my own sysadmin account after the failed attempt.
Is anyone able to provide information on what other minimum permissions the group might need to successfully enable them to setup a mirroring session?
Many thanks in advance,
Ben
This is a stack dump which is not necessarily permission based, what other messages, if any, are in the sql error log.
What version of sql server is running?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 29, 2015 at 11:26 am
I have tested today with exactly the same results on SQL Server 2014, 2008 R2 and 2008 instances.
I have now granted connect on the endpoint to the Windows NT group and have confirmed that the Windows NT group also belongs to the dbcreator server role which inherits the ALTER ANY DATABASE permission. For clarification here are the roles that the group has:
Server-Level: dbcreator, processadmin, public, securityadmin
Database-Level: public, db_owner
Mirroring Endpoint: grant connect
However members of the group are still unable to establish a database mirroring session and end up generating a stack dump with an access violation error and error 17310, severity 20, state 1 error.
October 30, 2015 at 2:15 am
You running any server audits?
October 30, 2015 at 4:32 am
stobe (2015-10-30 6:26 PM)
I have now granted connect on the endpoint to the Windows NT group
Why???
The account that requires connect on the endpoint is usually the account that the sql server service runs under. If not a domain user account then you must use certificated logins
stobe (2015-10-30 6:26 PM)
confirmed that the Windows NT group also belongs to the dbcreator server role which inherits the ALTER ANY DATABASE permission.
Again, why???
stobe (2015-10-30 6:26 PM)
For clarification here are the roles that the group has:Server-Level: dbcreator, processadmin, public, securityadmin
Database-Level: public, db_owner
Mirroring Endpoint: grant connect
However members of the group are still unable to establish a database mirroring session and end up generating a stack dump with an access violation error and error 17310, severity 20, state 1 error.
What account does the sql server service run under on each of the instances?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 30, 2015 at 4:43 am
Hi Perry,
Yes, I know that the SQL Service account is the one that needs CONNECT ON ENDPOINT as detailed at https://msdn.microsoft.com/en-us/library/ms179306.aspx, however as to why - if you read the thread then Anthony advised that the NT group might need it so I granted it as a test-case to see if that it was really needed.
You also ask why I have granted ALTER ANY DATABASE to the group, again because I was advised in the thread that this permission was needed and also found what seemed to be confirmation at http://msdn.developer-works.com/article/12736742/alter+database+set+partner++ and at https://social.msdn.microsoft.com/Forums/sqlserver/en-US/b46c1d20-4401-44ee-a4b8-ba72b8343d6a/alter-database-set-partner?forum=sqldatabasemirroring.
If you are knowing that these permissions are definitely not needed, please can you explain why and what you know to actually be needed?
The SQL Service account runs as a unique domain account, the same domain account is used on both Principal and Mirror and it has the correct permissions to the endpoint. As I already stated, a member of sysadmin is able to successfully setup a mirroring session proving that the problem is not with the endpoint.
October 30, 2015 at 5:39 am
stobe (10/30/2015)
Yes, I know that the SQL Service account is the one that needs CONNECT ON ENDPOINT as detailed at https://msdn.microsoft.com/en-us/library/ms179306.aspx, however as to why - if you read the thread then Anthony advised that the NT group might need it so I granted it as a test-case to see if that it was really needed.
If the endpoints already exist then users do not need the CREATE ENDPOINT permission, endpoints owned by users are especially troublesome if you wish to remove the account from sql server at a later date. The users do not need connect permission, only the ability to create and as I said above there are issues with this. Why are a group of users creating mirrored databases across your servers would be my question?
BTW, I have read the thread, look at my previous replies 😉
stobe (10/30/2015)
You also ask why I have granted ALTER ANY DATABASE to the group, again because I was advised in the thread that this permission was needed and also found what seemed to be confirmation at http://msdn.developer-works.com/article/12736742/alter+database+set+partner++ and at https://social.msdn.microsoft.com/Forums/sqlserver/en-US/b46c1d20-4401-44ee-a4b8-ba72b8343d6a/alter-database-set-partner?forum=sqldatabasemirroring.If you are knowing that these permissions are definitely not needed, please can you explain why and what you know to actually be needed?
The SQL Service account runs as a unique domain account, the same domain account is used on both Principal and Mirror and it has the correct permissions to the endpoint. As I already stated, a member of sysadmin is able to successfully setup a mirroring session proving that the problem is not with the endpoint.
If you wish to setup a mirror session on a database you do need alter permission, but again why are a large number of users creating mirror sessions on your sql server instances, it's not something I would be comfortable with
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 30, 2015 at 5:44 am
"If the endpoints already exist then users do not need the CREATE ENDPOINT permission,"
I didn't give them CREATE ENDPOINT, I gave them CONNECT ON ENDPOINT and I know it is likely not needed but testing it based on the advice of this thread is hardly doing any harm when I can just as easily REVOKE once I have confirmed what is needed.
No where did I say this was a large number of users in the group, it is a select few members of staff who work in 2nd/3rd line and require a little more access to perform database related tasks for our customers, like establishing mirroring sessions for new databases, but not so many permissions as to grant them sysadmin.
I have now done a test where I created a new SQL login, and gave it all available server-level permissions, endpoint permissions and db_owner permissions on the intended database. This should have effectively given it the same permissions as a login in the sysadmin role, however the login still could not connect the mirroring sessions unless I explicitly gave it the sysadmin role, upon which it would work. So sysadmin role has a permission that I am missing somewhere, but it is not ALTER DATABASE or ALTER ANY DATABASE as that has been given.
October 30, 2015 at 5:49 am
stobe (10/30/2015)
I didn't give them CREATE ENDPOINT, I gave them CONNECT ON ENDPOINT
I didn't say you did give them create, I'm pointing out they may need this on a new instance, but they certainly don't need connect
And as I said the stack dump is very unlikely a permission issue
stobe (10/30/2015)
No where did I say this was a large number of users in the group, it is a select few members of staff who work in 2nd/3rd line and require a little more access to perform database related tasks for our customers, like establishing mirroring sessions for new databases, but not so many permissions as to grant them sysadmin.
Assumption on my part but for good reason. A Windows NT group can have any number of users assigned, just be careful that you are able to manage this effectively
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 30, 2015 at 6:10 am
We are getting the same stack dump with an access violation error on multiple instances on different machines and on different SQL versions. The thread at http://msdn.developer-works.com/article/12736742/alter+database+set+partner++ also details our exact scenario and granting sysadmin to the group solves the issue, so it would seem permissions based. However I am still willing to give benefit of doubt and will explore the stack dump more.
November 5, 2015 at 9:18 am
Thanks for all the suggestions.
For those that are interested and for those poor souls who may encounter this in the future - I thought I'd come back and report that following logging a case with Microsoft Support, it has been confirmed that this was a known bug in SQL Server 2008 and SQL Server 2016. Following my case it is now officially logged (internally at MS at least) as a bug with SQL Server 2014.
The only currently reported workaround to allowing login accounts to be able to establish database mirroring sessions is to put them in the sysadmin server role.
November 6, 2015 at 5:34 am
so nothing to do with permissions then
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply