What's the maximum password size I can get on SQL Server 2012/2014? I was unable to find this information online.
I mean SQL Server credentials itself, not passwords inside tables.
Thanks a lot
azure-sql-databasepasswordsql server
What's the maximum password size I can get on SQL Server 2012/2014? I was unable to find this information online.
I mean SQL Server credentials itself, not passwords inside tables.
Thanks a lot
You have a few options (TL;DR -> working function is towards the end of Option #2, BUT also see Update section after Option #3 !):
If you are ok with just hex values (0 - 9 and A - F) you can just call CRYPT_GEN_RANDOM which was introduced in SQL Server 2008:
SELECT CRYPT_GEN_RANDOM(5) AS [Hex],
CONVERT(VARCHAR(20), CRYPT_GEN_RANDOM(5), 2) AS [HexStringWithout0x],
CONVERT(VARCHAR(20), CRYPT_GEN_RANDOM(10)) AS [Translated-ASCII],
CONVERT(NVARCHAR(20), CRYPT_GEN_RANDOM(20)) AS [Translated-UCS2orUTF16]
returns:
Hex HexStringWithout0x Translated-ASCII Translated-UCS2orUTF16
0x4F7D9ABBC4 0ECF378A7A ¿"bü<ݱØï 튄ꆠ䤅㫘ᓟ멿ৢ폫씎䛯
As you can see, you need to use the format option of 2
on the CONVERT
function so that it doesn't give you characters that, while more diverse, might be harder to type in, and you don't get the leading 0x
. But you can also see that even with a single row, multiple calls will get different return values (just like NEWID()
).
Please note the "length" values passed into the CRYPT_GEN_RANDOM
function. Since a hex byte is 2 characters, you only need to generate a 5-byte value. The CONVERT(VARCHAR...
uses a "length" of 10 since each hex byte becomes a character. And the CONVERT(NVARCHAR...
uses a "length" of 20 since every 2 bytes will become a character (when running this in a database with a default collation that does not end in _SC
, which is most cases).
You can always create your own algorithm and pass in the value of CRYPT_GEN_RANDOM
for the random aspect. AND, if you can structure your algorithm to be a single SELECT
such that it can be done in an Inline Table-Valued Function / iTVF (usually using CTEs), then you technically can use a non-deterministic function (at least effectively) since the function call will pass in the expression instead of the result of that expression, like it does with Multistatement TVFs and Scalar UDFs:
USE [tempdb];
GO
CREATE FUNCTION dbo.GeneratePassword_InlineTVF(@RandomValue VARBINARY(10))
RETURNS TABLE
AS RETURN
SELECT CONVERT(VARCHAR(20), @RandomValue, 2) +
'~~' +
CONVERT(VARCHAR(20), @RandomValue, 2) AS [HowRandomAmI?];
GO
CREATE FUNCTION dbo.GeneratePassword_MultistatementTVF(@RandomValue VARBINARY(10))
RETURNS @Result TABLE ([HowRandomAmI?] VARCHAR(100))
AS
BEGIN
INSERT INTO @Result ([HowRandomAmI?])
VALUES (CONVERT(VARCHAR(20), @RandomValue, 2) +
'~~' +
CONVERT(VARCHAR(20), @RandomValue, 2));
RETURN;
END;
GO
CREATE FUNCTION dbo.GeneratePassword_ScalarUDF(@RandomValue VARBINARY(10))
RETURNS VARCHAR(100)
AS
BEGIN
RETURN CONVERT(VARCHAR(20), @RandomValue, 2) +
'~~' +
CONVERT(VARCHAR(20), @RandomValue, 2);
END;
GO
And then run the tests:
SELECT * FROM tempdb.dbo.GeneratePassword_InlineTVF(CRYPT_GEN_RANDOM(10));
-- 27169EEC2B9CF7CC2731~~E9A95F49060BD41C0FF6
SELECT * FROM tempdb.dbo.GeneratePassword_MultistatementTVF(CRYPT_GEN_RANDOM(10));
-- 68EBC132814EA78602DE~~68EBC132814EA78602DE
SELECT tempdb.dbo.GeneratePassword_ScalarUDF(CRYPT_GEN_RANDOM(10)) AS [HowRandomAmI?];
-- 702DAF1C441C42FFBF5F~~702DAF1C441C42FFBF5F
And just to be sure about multiple rows in an iTVF:
SELECT TOP 5 rnd.[HowRandomAmI?]
FROM tempdb.dbo.GeneratePassword_InlineTVF(CRYPT_GEN_RANDOM(10)) rnd
CROSS JOIN [master].[sys].[objects];
returns:
HowRandomAmI?
---------------
1D57F0ABFDE44BCAED00~~AD57E9E2FF01768BB86F
264674BE1C9ABBC1572E~~6C75CD4D472935FDFA40
CB54CC6BB5F31F42FDEA~~B3EC7061027FCD36C9AC
44525355FC15655C1F4D~~4DDF874CD06BC4F2D7A4
0E51B6364F193F588C93~~08E2ED40ED9752267EF7
Putting all of this info into practice, we can do the following with regards to the original request:
CREATE FUNCTION dbo.GeneratePassword_Real(@RandomValue VARBINARY(30))
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN
WITH base(item) AS
(
SELECT NULL UNION ALL SELECT NULL UNION ALL SELECT NULL UNION ALL
SELECT NULL UNION ALL SELECT NULL UNION ALL SELECT NULL
), items(item) AS
(
SELECT NULL
FROM base b1
CROSS JOIN base b2
)
SELECT (
SELECT TOP (LEN(@RandomValue))
SUBSTRING('1234567890QWERTYUIOPASDFGHJKLZXCVBNM',
(CONVERT(TINYINT, SUBSTRING(@RandomValue, 1, 1)) % 36) + 1,
1) AS [text()]
FROM items
FOR XML PATH('')
) AS [RandomPassword];
And then execute it as follows:
DECLARE @PasswordLength INT = 10;
SELECT * FROM tempdb.dbo.GeneratePassword_Real(CRYPT_GEN_RANDOM(@PasswordLength));
CAUTION: Please see Item # 2 below related to running this for multiple rows!
You can create a SQLCLR function that would have access to randomizing functionality. The main disadvantage here is that a Scalar function is much more likely to have its return value cached than an iTVF.
Update
Given the newly provided context for usage of this function from the Question update:
The idea behind the function is that if somebody requires a password reset I could do the following:
UPDATE Users SET Password = dbo.GeneratePassword() WHERE UserID = @UserID
we can address the following concerns as expressed in a comment on this answer:
One needs to pass a parameter to the function with a random value. This enforces that it is necessary to know one way of creating random strings in order to use the function...
True. And it is admittedly slightly inconvenient. However, that does not negate this approach's ability to solve the issue at hand. It just requires some comments / documentation on how to properly use this function.
...the function couldn't be executed inside another function because you wouldn't be allowed to use a non deterministic function to generate random values inside the container function.
True. But there is no need to call this function within another function. This doesn't need to be a scalar function in order to be used in an UPDATE
:
USE [tempdb];
CREATE TABLE #Users (UserID INT NOT NULL PRIMARY KEY, [Password] VARCHAR(20));
INSERT INTO #Users (UserID, [Password]) VALUES (1, 'a'), (2, 'b');
DECLARE @PasswordLength INT = 10;
UPDATE tmp
SET tmp.[Password] = pass.RandomPassword
FROM #Users tmp
CROSS APPLY dbo.GeneratePassword_Real(CRYPT_GEN_RANDOM(@PasswordLength)) pass
WHERE tmp.UserID = 1;
SELECT * FROM #Users;
With regards to handling multiple rows (please see multi-row test in Option 2 above), functions of any type (user created, at least) -- Scalar UDF, Multistatement TVF, iTVF, SQLCLR UDF, or SQLCLR TVF -- are not guaranteed to run per row! You might need to get creative in how you convince the Query Optimizer to not cache the result, but there might be cases when nothing works. The following example shows the same iTVF being called twice. Passing in just @PasswordLength
causes the output value to be cached. However, when you pass in a formula that incorporates a field from the current row, then it might behave as you are expecting it to:
DECLARE @PasswordLength INT = 10;
SELECT pass.RandomPassword, pass2.RandomPassword
FROM [master].[sys].[objects] sac
CROSS APPLY tempdb.dbo.GeneratePassword_Real(CRYPT_GEN_RANDOM(@PasswordLength)) pass
CROSS APPLY tempdb.dbo.GeneratePassword_Real(CRYPT_GEN_RANDOM((sac.[object_id] % 1)
+ @PasswordLength)) pass2
returns:
RandomPassword RandomPassword
-------------- --------------
KX1M1NSKE7 8I1OK6TJI3
KX1M1NSKE7 S0RA6AWKAJ
KX1M1NSKE7 MKXV9UAB0Y
KX1M1NSKE7 4RQ2K6O1RP
KX1M1NSKE7 8J8PKJP7K2
The point made by @BaconBits in a comment on the question, regarding it being best to not store readable passwords, is a valid and good point. However, circumstances might prevent that option from being possible, or at least possible at this point in time.
This is because since SQL Server 2012 the hash algorithm has changed.
Starting from this version, the hash is twice as long.
You can read up on the three hash versions that are used for the various versions of SQL Server in the sp_help_revlogin microsoft article, under Remarks:
A password can be hashed in three ways:
VERSION_LEGACY: This hash is a 16-byte pre-SQL Server 2000 hash.
VERSION_SHA1: This hash is generated by using the SHA1 algorithm and is used in SQL Server 2000 through SQL Server 2008 R2.
VERSION_SHA2: This hash is generated by using the SHA2 512 algorithm and is used in SQL Server 2012.
Which means that if you want to move a SQL Server 2012 or higher user to a 2008r2 instance, you'll need to find a different method.
I tested it quite a bit now. Any user created under SHA1 when moved using the password hash to a new version server (2012+) can then be moved back as long as the password isn't changed. However any new user created in 2012+ will not be moveable to 2008r2 or prior.
Best Answer
https://msdn.microsoft.com/en-us/library/ms189751.aspx