April 5, 2017 at 8:14 am
Hi
I have created a stored proc for the C# web developer to use that takes a dotted firstname.lastname syntax or an AD account as a parameter, and returns via output parameters whether the passed login is a user of the system and the role within the system. Can someone point me to an article that I can share with the web developer that allows non domain users logging onto the site to be passed as SQL security users when presented with a form for username / password; and AD accounts that are passed through
April 5, 2017 at 8:52 am
graham.measures - Wednesday, April 5, 2017 8:14 AMHi
I have created a stored proc for the C# web developer to use that takes a dotted firstname.lastname syntax or an AD account as a parameter, and returns via output parameters whether the passed login is a user of the system and the role within the system. Can someone point me to an article that I can share with the web developer that allows non domain users logging onto the site to be passed as SQL security users when presented with a form for username / password; and AD accounts that are passed through
I'm not entirely clear on exactly what you're looking for. If you're seeking to understand the difference between a SQL login and the use of an AD account and Windows authentication, there's ample material available that's just a Google search away. If you're looking for an article on how to present a form on a website that can accept either an AD account or a firstname.lastname format (to allow creation of a SQL Login), that has any of a number of issues without having a great deal more context.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
April 5, 2017 at 9:17 am
+xgraham.measures - Wednesday, April 5, 2017 8:14 AMHi
I have created a stored proc for the C# web developer to use that takes a dotted firstname.lastname syntax or an AD account as a parameter, and returns via output parameters whether the passed login is a user of the system and the role within the system. Can someone point me to an article that I can share with the web developer that allows non domain users logging onto the site to be passed as SQL security users when presented with a form for username / password; and AD accounts that are passed throughI'm not entirely clear on exactly what you're looking for. If you're seeking to understand the difference between a SQL login and the use of an AD account and Windows authentication, there's ample material available that's just a Google search away. If you're looking for an article on how to present a form on a website that can accept either an AD account or a firstname.lastname format (to allow creation of a SQL Login), that has any of a number of issues without having a great deal more context.
Hi Steve
We are interested in a form on the web site where the user types in a name and password so we can map username/password to SQL Security and invoke the stored proc below. If we detect using.net that the user is logged into the domain, we will not show the form for login & password and will allow the user access to the application, after first passing the credentials to the stored proc below.
This is my stored proc:-
CREATE PROC [Base].[RequestUserDetails]
@ADaccountNameOrUserName NVARCHAR(128)
,@Password BINARY(64)=NULL
,@IsRPSuser BIT OUTPUT
,@PasswordNoMatch BIT=0 OUTPUT
,@AccountLockedOut BIT=0 OUTPUT
,@UserRole VARCHAR(15) OUTPUT
AS
DECLARE @TryNumber INT
SELECT
@IsRPSuser=1
,@PasswordNoMatch=NULL
,@UserRole=UserRole
,@AccountLockedOut=0
FROM Base.Users
WHERE ADAccountName IS NOT NULL
AND ADAccountName = @ADaccountNameOrUserName
IF @@ROWCOUNT > 0
RETURN 0
SELECT
@IsRPSuser=1
,@PasswordNoMatch=0
,@UserRole=UserRole
,@AccountLockedOut=0
FROM Base.Users
WHERE (UserNameDotted=@ADaccountNameOrUserName AND EncryptedPassword=@Password)
IF @@ROWCOUNT > 0
RETURN 0
SELECT
@IsRPSuser=1
,@PasswordNoMatch=NULL
,@UserRole=''
,@AccountLockedOut=0
FROM Base.Users
WHERE (UserNameDotted=@ADaccountNameOrUserName AND EncryptedPassword IS NULL)
IF @@ROWCOUNT > 0
RETURN 0
SELECT
@IsRPSuser=1
,@PasswordNoMatch=1
,@UserRole=''
,@AccountLockedOut=0
FROM Base.Users
WHERE (UserNameDotted=@ADaccountNameOrUserName AND EncryptedPassword IS NOT NULL)
IF @@ROWCOUNT > 0
BEGIN
UPDATE Base.Users
SET TryNumber = ISNULL(TryNumber,0)+1
WHERE UserNameDotted=@ADaccountNameOrUserName
SELECT @TryNumber= TryNumber FROM Base.Users WHERE UserNameDotted=@ADaccountNameOrUserName
IF @TryNumber >= 3
BEGIN
UPDATE Base.Users
SET AccountLocked = SYSDATETIME()
WHERE UserNameDotted=@ADaccountNameOrUserName
SET @AccountLockedOut=1
IF @TryNumber > 10
WAITFOR DELAY '00:00:02'
-- a scheduled task resets locked out accounts when the sysdatetime() - AccountLocked datetime > 31 minutes
END
ELSE
SET @AccountLockedOut=0
RETURN 0
END
SELECT
@IsRPSuser=0
,@PasswordNoMatch=NULL
,@UserRole=NULL
RETURN -1
GO
April 5, 2017 at 10:51 am
As you have the stored procedure, I guess the question is, what help are you looking for? This is a SQL Server forum as opposed to a web-help kind...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply