Logon - SQL Login vs. Trusted Connection

tymberwyld posted on Monday, August 07, 2006

I remember someone talking about this before, but now I can't find the forum threads anymore.  What I need to do is handle Windows Integrated logins as well as just plain SQL Logins.  This is a Windows Forms app.

Here's how it should go:

1. User opens the app
2. The application attempts to authenticate the user through the data portal by using their Windows logon (current Windows Identity).  I am thinking this could be a command or something.
   
3. If the Authentication was successful I would get back a User object, if it fails (IsAuthenticated = False), the application will prompt the user with a Logon dialog to ask for their credentials.
4. The Logon dialog will then authenticate the user (going through the same process in step 2 but using a CustomPrincipal instead).  Step 3 then occurs again until the user is successfully logged in.

Does anyone have a suggestion or similar requirement that I could piggy-back on? ;-)

RockfordLhotka replied on Monday, August 07, 2006

I did alter the data portal (in 1.5 I think) to make this possible. If you look at the client-side data portal code you'll see that it checks for a non-required appSettings value to force the passing of the Windows identity token to the server, along with using custom authentication.

What that setting actually does is causes BOTH Windows security (client-side credentials must match on server) and custom authentication to become active at the same time. I think that's what you are asking for?

To use this you don't need to do a special Command object. The Login() method for custom authentication will attempt to talk to the server through the data portal - which of course will require that the Windows credentials be valid. Two birds with one stone.

tymberwyld replied on Tuesday, August 08, 2006

So is that code / logic in the existing 2.0.3 implementation as well?  Or do I need to download 1.5?

RockfordLhotka replied on Tuesday, August 08, 2006

---

From: tymberwyld [mailto:cslanet@lhotka.net]
Sent: Tuesday, August 08, 2006 8:42 AM
To: rocky@lhotka.net
Subject: Re: [CSLA .NET] Logon - SQL Login vs. Trusted Connection

So is that code / logic in the existing 2.0.3 implementation as well?  Or do I need to download 1.5?

tymberwyld replied on Tuesday, August 08, 2006

Actually, I don't think this is what I want (or maybe I'm not understanding how this works).  What I am trying to do is build a Connection String dynamically at execution time.  This is required because the user can change the Database / Server on the Logon Form's configuration.  I don't think this is the same as Windows vs. Custom authentication.  This will all work fine on a Local DataPortal, but once it's Remote or WebService, the SqlConnection object won't have the correct credentials.

What I really want to do is have a static "GetConnectionString(DataPortalContext context)" method on the DataPortal to build the connection string from the User's credentials and the Database and Server properties that will be added to the DataPortalContext (or maybe stored in the ClientContext HybridDictionary).

Here's my question.  If the DataPortal / Business object "Fetch" method is running on the server, how can I set the current credentials to a WindowsPrincipal or CustomPrincipal dynamically in order to build a ConnectionString that is:

1. Data Source=(local);Initial Catalog=MyDB;Integrated Security=SSPI
   OR
   
2. Data Source=(local);Initial Catalog=MyDB;User ID=myuser;Password=mypwd
   depending on the Credentials.

If I use ConnectionString #1 on the remote DataPortal, isn't it just going to use the Server's credentials not the credentials I am passing it?  If I set the "Thread.CurrentPrincipal = ?" on the remote DataPortal will it then attempt to login using the User's WindowsPrincipal?

I'm really confused!

ajj3085 replied on Tuesday, August 08, 2006

Why not just store the server chosen in the ApplicationContext.ClientContext?  It will get 'shipped' to the server and you can read it in your DataPortal methods.  You could do the same with the security settings.

RockfordLhotka replied on Tuesday, August 08, 2006

Ahh sorry, I missed that you were talking about database logins, not app server logins...

tymberwyld replied on Wednesday, August 09, 2006

Exactly!  I am NOT trying to Authenticate the application / user credentials on the Server.  I am trying to use these "Credentials" for logging into SQL Server.  So this is more like a Database Authentication.  It's kind of like the "Partial Trust" model in that the application will "assume" that everyone has access to the application until it hits the database.  The first layer of authentication will be if they actually have a valid login into Sql Server.  The 2nd level will be that we will pull their Roles from the Database.

I've come to the conclusion that I cannot use the "WindowsIdentity" by itself because I wouldn't be able to return valid roles from the database (the WindowsIdentity would return only Windows Roles / Groups).  What I plan to do is create a new IIdentity object that stores a WindowsIdentity internally.  This new Identity will also have a Property that returns an Enum of how the Identity was actually able to connect to the server (Windows Auth vs. a straight Sql Logon) which will be used for subsequent connections so that it doesn't have to keep being discovered.

And another thing.  Why did Microsoft make Authentication is WinForms totally different than in WebForms?  For example, ASP.NET can use the System.Net.AuthenticationManager which attempts to authenticate using different IAuthenticationModules.  It would have been nice if I could have re-used this AuthenticationManager in WinForms and just built a "SqlAuthenticationModule" that implemented the IAuthenticationModule but Nooooo...this AuthenticationManager only accepts WebRequests / Responses!  Why did they make the System.Net.Credentials and the System.Security classes totally seperate?  They're the SAME exact thing (theoretically)!  Arghhh!

JHurrell replied on Wednesday, August 09, 2006

Is it a simple matter of determining the identity of the currently logged in user and passing that to a wrapper that dynamically builds that connection string?

If you, you can determine who is currently logged in with this: System.Security.Principal.WindowsIdentity.GetCurrent().Name.ToString();

One you have the name, you can pass that to IPrincipal.Authenticate(). This will allow you to do whatever magic you need to determine if they have been established as a trusted user or one that might perhaps have some kind of visitor access.

We do this with S3 (Single Sign-on). When a user tries to access our site, we can retrieve their ID from the HTTP headers given to us by S3. With that name, we call IPrincipal.Authenticate() and if Authenticated, great, they have access to the site and we've retrieved their roles. If not, the user is considered a visitor and will have very limited access.

- John

RockfordLhotka replied on Wednesday, August 09, 2006

I've come to the conclusion that I cannot use the "WindowsIdentity" by itself because I wouldn't be able to return valid roles from the database (the WindowsIdentity would return only Windows Roles / Groups).  What I plan to do is create a new IIdentity object that stores a WindowsIdentity internally.  This new Identity will also have a Property that returns an Enum of how the Identity was actually able to connect to the server (Windows Auth vs. a straight Sql Logon) which will be used for subsequent connections so that it doesn't have to keep being discovered.

tymberwyld replied on Wednesday, August 09, 2006

RockfordLhotka:

Yes, I think that should work. Just make sure to avoid holding an instance-level reference to the WindowsIdentity, as it isn't serializable.

 

Rocky

Well, not entirely true.  The WindowsIdentity does Implement the ISerializable interface but it can only be serialized to binary and back.  I've tested this and it works.  I need to play around and see how this is all going to come together...

RockfordLhotka replied on Wednesday, August 09, 2006

Well, not entirely true.  The WindowsIdentity does Implement the ISerializable interface but it can only be serialized to binary and back.  I've tested this and it works.  I need to play around and see how this is all going to come together...