Sql-server – SQL 2014: Add Table gives “The requested operation cannot be completed. The computer must be trusted”

sql server

A single user is not able to "Add Table" to a database that he (or anyone else) created. Other users can add tables.

We have checked permissions and they are the same – but obviously we've missed soemthing. Can anyone provide a hint as to where we should be looking to resolve this?

Details

SQL Server 2014, Windows Server 2012 R2, Domain Auth
Remote desktop in to the server; Windows authentication over a domain.
Launch SQL Management Studio
Add Database (or use an existing database created by another user)
Add Table: fails with message as below:

The requested operation cannot be completed. The computer must be trusted for delegation and the current user account must be configured to allow delegation.
(SQLEditors)

Full stack trace of the error is:

Program Location:

at Microsoft.SqlServer.Management.Data.DataProtection.EncryptString(String s)
at Microsoft.SqlServer.Management.DataTools.DataServices.DataConnection.SetConnectionStrings(DataConnectionProperties connectionProperties)
at Microsoft.SqlServer.Management.DataTools.DataServices.DataConnection.SetConnectionStrings(String connectionString)
at Microsoft.SqlServer.Management.DataTools.DataServices.DataConnection..ctor(DataProvider provider, String connectionString, Boolean encryptedString)
at Microsoft.SqlServer.Management.DataTools.DataServices.DataConnectionFactory.CreateDataConnectionImpl(Guid provider, String connectionString, Boolean encryptedString)
at Microsoft.SqlServer.Management.Data.DataConnectionFactory.CreateDataConnection(Guid provider, String connectionString, Boolean encryptedString)
at Microsoft.SqlServer.Management.DataTools.DataServices.DataConnectionManager.CreateDataConnection(Guid provider, String connectionString, Boolean encryptedString)
at Microsoft.SqlServer.Management.Data.DataConnectionManager.GetDataConnection(Guid provider, String connectionString, Boolean encryptedString, Boolean update)
at Microsoft.SqlServer.Management.DataTools.DataServices.DataConnectionManager.Microsoft.SqlServer.Management.Data.Interop.IVsDataConnectionManager.GetDataConnection(Guid& guidProvider, String bstrConnectionString, Boolean fEncryptedString)
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDataDesignerNode.CreateDesigner()
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDataDesignerNode.Open()
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VirtualProject.CreateDesigner(Urn origUrn, DocumentType editorType, DocumentOptions aeOptions, IManagedConnection con, String fileName)
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VirtualProject.Microsoft.SqlServer.Management.UI.VSIntegration.Editors.ISqlVirtualProject.CreateDesigner(Urn origUrn, DocumentType editorType, DocumentOptions aeOptions, IManagedConnection con, String fileName)
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.ISqlVirtualProject.CreateDesigner(Urn origUrn, DocumentType editorType, DocumentOptions aeOptions, IManagedConnection con, String fileName)
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDocumentMenuItem.CreateDesignerWindow(IManagedConnection mc, DocumentOptions options)

We have noticed that it is directly related to the user account:

  • Connecting to database (In Management Studio) using either Windows Authentication or SQL User makes no difference.

  • Another user (with apparently the same permissions) can RDC in from the same host computer and operate without problem.

  • The user with issues still has issues even when RDC from another host computer.

Therefore it's the user that RDC's in / launched Mangement Studio that has the issue.

The only clue we can find is at https://technet.microsoft.com/en-au/library/cc739474(v=ws.10).aspx – but this suggests that delegation is for service accounts, not user accounts.

What permission/setting have we missed? Thanks.

Best Answer

Edited Jan 2016: Possible solution below.

Original Response

For anyone else looking for this the answer appears to be because of "SimpleAD" (http://docs.aws.amazon.com/directoryservice/latest/simple-ad/what_is.html)

SimpleAD is supposed to be a free drop-in replacement for Windows Active Directory, but it looks like there are some differences. Tracked down via this forum post: https://forums.aws.amazon.com/thread.jspa?messageID=633745

Solution: - Replace using SimpleAD with a real version (obviously expensive and a lot of work), OR - Live with it. It appears to be an old bug that won't be fixed quickly, judging by other comments.

Not perfect, but I hope that helps someone if they have the same issues.

Update: Jan 2016

Reading the following has fixed the issue for us:

https://www.suse.com/support/kb/doc.php?id=7016786

In this Microsoft document you will find their suggested workaround. https://support.microsoft.com/en-us/kb/3000850

Here is some of the information from that document.

Workaround To work around this problem, set the value of the ProtectionPolicy registry entry to 1 to enable local backup of the MasterKey instead of requiring a RWDC in the following registry subkey: HKEY_LOCAL_MACHINE\Software\Microsoft\Cryptography\Protect\Providers\df9d8cd0-1501-11d1-8c7a-00c04fc297eb

Based in that information, it is obvious that this would need to be done on each Windows 8.1 client that has been updated with KB2992611 or KB3000850.

Appears to works for SQL management studio, but Visual Studio can't attach a process or debug.

Related Topic