0
Completed

Connectivity to SQL databases using a specified windows account

Bob Bradley 8 years ago • updated by anonymous 3 years ago 25

When ADO.Net is used to connect to a SQL store, the only "Windows Integrated Security" option available for a windows service is to use the windows service account itself. This is overly restrictive, especially for DET where there are a number of XMAs connecting to SQL databases with credentials other than the FIM sync service, which is also used as the Event Broker 2.3 service account. This has been achieved through using a SQL.Data.Sqlclient connection in lieu of ADO.Net ... as per this TechNet article: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnectionstringbuilder.aspx ... and as explained in this 3rd party tutorial: http://www.dotnetperls.com/sqlclient

We talked about this on Friday Matthew... can you start off by determining how much of an impact this would have if we decided to introduce it?

Tony, please do some discovery for this issue.

Please look at how EB 2.x used to do this. Research and document the methods that were previously used, and the impact of incorporating them into EB 3.0. We will most likely offer a choice between the two methods, as I believe the new method is superior, except for missing the desired functionality mentioned by Bob.

Thanks.

As mentioned above the method by which connections were made to SQL servers in EB2.x was through a System.Data.SqlClient SqlConnection.

The requirements of this SqlConnection are the same as the method currently employed to connect to SQL servers as of the most recent version of EB. In and of that, a reader only requires a:

  • Connection string
  • SQL Query
  • (Optional) Timeout
    All of which are already available to the SQL Changes/Execute plugins.

As for the actual impact of incorporating these changes into EB3.0, they will be fairly minor, with the exception that each plugin implementing these changes will need to have a mechanism for choosing between either of the connection methods; that or paired plugins will need to be created to facilitate both connection methods.

Reassigned for perusal.

If it only requires the same details as the current operation, I'm happy for you to proceed.

Please duplicate the changes and the execute plugins, as there is already a common base class for you to use.

Create an extra configuration option in the agent UseLegacyConnectionMethod or similar. The plugin factories should use this property to determine which of the operations to use.

The agent factories and adapters will have to be updated, along with the agent views and view informations.

Bob, your description was not helpful.

The links you mention refer to the method we have used, ie SqlConnection, which takes a connection string. This also happens to be the exact method used in EB 2.x.

The agent allows for either Windows authentication, or SQL authentication. If you do not wish to allow access for the service account, create a SQL login. As with EB 2.x.

Am I missing something?

Thanks.

Adam

The key here is that without this capability there are various production deployment scenarios whereby a FIM SQL MA will be able to be configured but Event Broker will not ... e.g. (to best illustrate my point) when the SQL server is in a different (untrusted) AD domain to the domain account identity of the Event Broker service.

In posting this issue I tried to draw your attention to the way I was able to overcome a similar dilemma when writing a SQL XMA - by using the Native SQL 2005/2008 Client. I am sorry this was not helpful.

Since our phone conversation I've had a chance (on the plane) to think further about this, recalling that you made a statement something like "I will not be making this change and compromising the product", and provided 2 specific objections by way of your reasons:
1. EvB 2.* does not support this
2. By storing credentials in the product you will be violating some sort of certification criteria.

Have I got that right?

I am now looking at a copy of the EvB 2.2 plug-in code and I can see how the limitations are solely determined by those of the specified connection string ... for some reason I was under the impression that a username/password xml node could be explicitly set up for this plug-in, but unlike the AD Changes plug-in (which had to support an untrusted domain connection for TAFE users @ DET) obviously not for this one. My knowledge of connection strings is that specifying a username/password only EVER works for native SQL authentication - a mode no longer supported in most production SQL sites due to its generally accepted inferior security model compared to Windows auth. The published MS SQL Best Practices document says as much here: http://download.microsoft.com/download/8/5/e/85eea4fa-b3bb-4426-97d0-7f7151b2011c/SQL2005SecBestPract.doc

Consequently I believe we too are supporting mixed mode authentication purely on a "legacy support" basis.

However, I don't see how providing the username/domain/password paramameters that would be required by the SQL2008 client to implement delegated WINDOWS authentication would be any different to the precedent already set by the AD Changes Plug-in. I would actually argue that the intent of EvB 2.* was always to provide this capability, but that it never worked - simply because of the way the connection was implemented, which is why I believe it (ultimately at least) needs to change.

Despite the ability to connect with different windows credentials to the Event Broker service being a very valid use case for the SQL changes plug-in, it is not a show-stopper purely because version 2.* is now known not to support this. Therefore, if this feature cannot (or will not for reasons yet to be documented) be incorporated in this version, it should definitely be considered in the earliest possible subsequent update.

Hi Bob,

Firstly, when Adam said the description was not helpful he was referring to the links you included... the MSDN article you provided is for the standard ADO.NET stuff we already use that and I can't see how it supports what you're asking for at all.

Secondly, the fact that the previous version does not support this makes it a moot point for the time being because I've mentioned many times that requests for new features have been closed for over 6 weeks now on Mark's instruction. The only reason we're having this discussion at all is because when you initially reported this to me over the phone you told me that 2.x was capable of this which we've now learnt is incorrect. I disagree with your assertion that it was the "intent" of the previous version - it was never an intended feature and to say otherwise is very deceptive.

Supplying custom windows credentials is arguably more unsafe than using SQL Server authentication. I realize you disagree with this, but the fact that the latest versions of ADO.NET connection strings do not allow you to specify custom Windows credentials is one such example of why it should not be done this way.

I can assure you that we WILL revisit and reexamine this issue in the future, but let me be very clear - we will not now, nor will we ever incorporate a feature in to a commercial product just because a client wants it, especially if they only want it as a result of a bad design. It would be the equivalent of displaying password details in our UI in plain text so administrators can retrieve them if they forget them (which is something I'm told DET wants!). There are a number of things we use to decide whether new features will be made, including, but not limited to: feasibility, cost, security, benefit, urgency, demand, Microsoft certification and many others. What one of our clients want is just a very small part of it.

As I said, we will revisit it in the future, but if we still decide that this is not the correct way to proceed then I expect you to accept our decision and advise others that it is the correct one.

Patrick - "very deceptive" ... totally untrue. Not trying to deceive at all. This business case is totally valid, and it's not about a customer being stupid either. Let's just work through this one without all the emotion when we can get a clear run at it later. Product requirements vs Solutions requirements ...

Important context note:

As of FIM Update 1, the "Microsoft SQL Server 2008 Native Client" is now a prerequisite of the FIM Sync Service ... so there's the precedent for Event Broker if we need one. See http://technet.microsoft.com/en-us/library/ff512686(WS.10).aspx where it says the following:

If you are upgrading the FIM Sync Service and are using the separated SQL DB topology, you may encounter error 25070, "Error connecting to database FIMSynchronizationService: Invalid class string". If this occurs, you may not be running the most recent version of the Microsoft SQL Server 2008 Native Client. To address this issue, install the Microsoft SQL Server 2008 Feature Pack, April 2009, and re-run the FIM Synchronization Service installer.

As the originally linked articles are using the same classes that are already used, and they do not offer the required functionality, the WindowsImpersonationContext class may be of use:
http://msdn.microsoft.com/en-us/library/system.security.principal.windowsimpersonationcontext.aspx

Before using the above method, look at the Framework Core Sql impersonate provider, as it may have already done something similar.

Tony, this will involve moving the SQL operations onto the Framework SQL providers, and adding UI for the missing impersonate provider.

Estimate increased to accommodate new requirements:

  1. Prove that the Framework impersonate components will be suitable.
  2. Migrate the Event Broker SQL operations to the Framework components. Update EB-519, as that will happen as a result. (Also link to this issue if this is all possible).
  3. Create UI to allow for the creation of the components, keep in mind that the components will be moved to Framework once EB-428 has been complete. Please create an issue linking to EB-428 for the move of this component to Framework.

This is not possible with the described implementation, namely the scope used by this component only works for impersonation of local users.

http://msdn.microsoft.com/en-us/library/windows/desktop/aa378184(v=vs.85).aspx

"You cannot use LogonUser to log on to a remote computer."

Tony, I believe that it can only impersonate users that can log-on to the local machine. Therefore, the local machine would need to be a domain member in order to impersonate a domain user. Are you saying that this isn't possible for the use case that Bob states?

The requested behaviour as of the comment mentioning:

when the SQL server is in a different (untrusted) AD domain to the domain account identity of the Event Broker service.

wouldn't be supported by the the impersonation scope implementation.

Tony, I don't think the "You cannot use LogonUser to log on to a remote computer" matters. We are trying to log a user on to the current machine.

There has been no progress on this issue following previous comment.

Removed the following test:

[Test]
public void TestTest1()
{
	var plugin = new SQLServerChangesPlugIn(
					"Server=prdgrp-test1;Initial Catalog=FIMSynchronizationService;",
					"SELECT 1 FROM mms_connectorspace",
					Timeout);
	Assert.IsTrue(plugin.Check());
}

External dependencies are typically not allowed. Please update the unit test to work on a local database. This will likely require a Clone.ps1 style script like Identity Broker.

Tony, as discussed, please look at how this issue would impact EB-519.

If this issue would allow for the Agent to use a configurable Framework component to handle what type of database, then it might allow the operation to not care what type of database it is connecting to.

See the comment on 05/Oct/12 4:12 PM.

Thanks.

This component needs to be updated to maintain XML compatible with the IAdoNetDataControlFactories and use the framework UI in UFCORE-64.