FORM AUTHENTICATION SSRS sql connection error
-
Hi i am implementing SSRS Form Authentication by following http://www.codeproject.com/Articles/675943/SSRS-Forms-Authentication https://msftrsprodsamples.codeplex.com/wikipage?title=SS2012!Security%20Extension%20Sample http://bharathonsqlserver.blogspot.sg/2012/09/forms-authentication-in-ssrs-2012.html and I am stuck at a point where I am trying to communicate with SQL Server. I have created the UserAccounts tablet for storing user credentials. But I wasn't able to connect and store values inside. Any of you might haven gone through this path, right? I am hoping to get it solved so that I can proceed further. Whenever I try to register, it fails: no error message at all.
Can someone please check my codes and remind me what it is wrong here?
This is when I log in with incorrect credentials.
Nothing is updated to my UserAccountsDb even after register and update username in config file as instructed in above sample tutorials.
Those are my AuthenticationExtension.cs andAuthenticationUtilites.cs
This is AuthenticationExtension.cs
using System;
using System.Data;
using System.Data.SqlClient;
using System.Security.Principal;
using System.Web;
using Microsoft.ReportingServices.Interfaces;
using System.Globalization;
namespace Microsoft.Samples.ReportingServices.CustomSecurity
{
public class AuthenticationExtension : IAuthenticationExtension
{
/// <summary>
/// You must implement SetConfiguration as required by IExtension
/// </summary>
/// <param name="configuration">Configuration data as an XML
/// string that is stored along with the Extension element in
/// the configuration file.</param>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Security", "CA2123:OverrideLinkDemandsShouldBeIdenticalToBase")]
public void SetConfiguration(String configuration)
{
// No configuration data is needed for this extension
}
/// <summary>
/// You must implement LocalizedName as required by IExtension
/// </summary>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Security", "CA2123:OverrideLinkDemandsShouldBeIdenticalToBase")]
public string LocalizedName
{
get
{
return null;
}
}
/// <summary>
/// Indicates whether a supplied username and password are valid.
/// </summary>
/// <param name="userName">The supplied username</param>
/// <param name="password">The supplied password</param>
/// <param name="authority">Optional. The specific authority to use to
/// authenticate a user. For example, in Windows it would be a Windows
/// Domain</param>
/// <returns>true when the username and password are valid</returns>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Security", "CA2123:OverrideLinkDemandsShouldBeIdenticalToBase")]
public bool LogonUser(string userName, string password, string authority)
{
return AuthenticationUtilities.VerifyPassword(userName, password);
}
/// <summary>
/// Required by IAuthenticationExtension. The report server calls the
/// GetUserInfo methodfor each request to retrieve the current user
/// identity.
/// </summary>
/// <param name="userIdentity">represents the identity of the current
/// user. The value of IIdentity may appear in a user interface and
/// should be human readable</param>
/// <param name="userId">represents a pointer to a unique user identity
/// </param>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Security", "CA2123:OverrideLinkDemandsShouldBeIdenticalToBase")]
////////public void GetUserInfo(out IIdentity userIdentity, out IntPtr userId)
////////{
//////// // If the current user identity is not null,
//////// // set the userIdentity parameter to that of the current user
//////// if (HttpContext.Current != null
//////// && HttpContext.Current.User != null)
//////// {
//////// userIdentity = HttpContext.Current.User.Identity;
//////// }
//////// else
//////// // The current user identity is null. This happens when the user attempts an anonymous logon.
//////// // Although it is ok to return userIdentity as a null reference, it is best to throw an appropriate
//////// // exception for debugging purposes.
//////// // To configure for anonymous logon, return a Gener
//////// {
//////// System.Diagnostics.Debug.Assert(false, "Warning: userIdentity is null! Modify your code if you wish to support anonymous logon.");
//////// throw new NullReferenceException("Anonymous logon is not configured. userIdentity should not be null!");
//////// }
//////// // initialize a pointer to the current user id to zero
//////// userId = IntPtr.Zero;
////////}
public void GetUserInfo(out IIdentity userIdentity, out IntPtr userId)
{
// If the current user identity is not null,
// set the userIdentity parameter to that of the current user
if (HttpContext.Current != null
&& HttpContext.Current.User != null)
{
userIdentity = HttpContext.Current.User.Identity;
}
else
// The current user identity is null. This happens when the user attempts an anonymous logon.
// Although it is ok to return userIdentity as a null reference, it is best to throw an appropriate
// exception for debugging purposes.
// To configure for anonymous logon, return a Gener
{
userIdentity = new GenericIdentity("test");
System.Diagnostics.Debug.Assert(true);
}
// initialize a pointer to the current user id to zero
userId = IntPtr.Zero;
}
/// <summary>
/// The IsValidPrincipalName method is called by the report server when
/// the report server sets security on an item. This method validates
/// that the user name is valid for Windows.The principal name needs to
/// be a user, group, or builtin account name.
/// </summary>
/// <param name="principalName">A user, group, or built-in account name
/// </param>
/// <returns>true when the principle name is valid</returns>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Security", "CA2123:OverrideLinkDemandsShouldBeIdenticalToBase")]
public bool IsValidPrincipalName(string principalName)
{
return VerifyUser(principalName);
}
//
[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2201:DoNotRaiseReservedExceptionTypes"), System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Reliability", "CA2000:DisposeObjectsBeforeLosingScope")]
public static bool VerifyUser(string userName)
{
bool isValid = false;
using (SqlConnection conn = new SqlConnection(
"Server=SERVERNAME;" +
"Integrated Security=SSPI;" +
"database=UserAccounts"))
{
SqlCommand cmd = new SqlCommand("LookupUser", conn);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter sqlParam = cmd.Parameters.Add("@userName",
SqlDbType.VarChar,
255);
sqlParam.Value = userName;
try
{
conn.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
// If a row exists for the user, then the user is valid.
if (reader.Read())
{
isValid = true;
}
}
}
catch (Exception ex)
{
throw new Exception(string.Format(CultureInfo.InvariantCulture,
CustomSecurity.VerifyError + ex.Message));
}
}
return isValid;
}
}
}
This is AuthenticationUtilities.cs
using System;
using System.Data;
using System.Data.SqlClient;
using System.Security.Cryptography;
using System.Text.RegularExpressions;
using System.Web.Security;
using System.Management;
using System.Xml;
using System.Text;
using System.Globalization;
namespace Microsoft.Samples.ReportingServices.CustomSecurity
{
[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Performance", "CA1812:AvoidUninstantiatedInternalClasses")]
internal sealed class AuthenticationUtilities
{
// The path of any item in the report server database
// has a maximum character length of 260
private const int MaxItemPathLength = 260;
private const string wmiNamespace = @"\root\Microsoft\SqlServer\ReportServer\RS_MSSQLSERVER\v11";
private const string rsAsmx = @"/ReportService2010.asmx";
// Method used to create a cryptographic random number that is used to
// salt the user's password for added security
internal static string CreateSalt(int size)
{
// Generate a cryptographic random number using the cryptographic
// service provider
RNGCryptoServiceProvider rng = new RNGCryptoServiceProvider();
byte[] buff = new byte;
rng.GetBytes(buff);
// Return a Base64 string representation of the random number
return Convert.ToBase64String(buff);
}
// Returns a hash of the combined password and salt value
internal static string CreatePasswordHash(string pwd, string salt)
{
// Concat the raw password and salt value
string saltAndPwd = String.Concat(pwd, salt);
// Hash the salted password
string hashedPwd = FormsAuthentication.HashPasswordForStoringInConfigFile(
saltAndPwd, "SHA1");
return hashedPwd;
}
// Stores the account details in a SQL table named UserAccounts
[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2201:DoNotRaiseReservedExceptionTypes"), System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Reliability", "CA2000:DisposeObjectsBeforeLosingScope")]
internal static void StoreAccountDetails(string userName,
string passwordHash,
string salt)
{
// See "How To Use DPAPI (Machine Store) from ASP.NET" for
// information about securely storing connection strings.
using (SqlConnection conn = new SqlConnection(
"Server=SERVERNAME\\MSSQLSERVER;" +
"Integrated Security=SSPI;" +
"database=UserAccounts"))
{
SqlCommand cmd = new SqlCommand("RegisterUser", conn);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter sqlParam = null;
sqlParam = cmd.Parameters.Add("@userName", SqlDbType.VarChar, 40);
sqlParam.Value = userName;
sqlParam = cmd.Parameters.Add(
"@passwordHash", SqlDbType.VarChar, 50);
sqlParam.Value = passwordHash;
sqlParam = cmd.Parameters.Add("@salt", SqlDbType.VarChar, 10);
sqlParam.Value = salt;
try
{
conn.Open();
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
// Code to check for primary key violation (duplicate account
// name) or other database errors omitted for clarity
throw new Exception(string.Format(CultureInfo.InvariantCulture,
CustomSecurity.AddAccountError + ex.Message));
}
}
}
// Method that indicates whether
// the supplied username and password are valid
[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2201:DoNotRaiseReservedExceptionTypes"), System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Reliability", "CA2000:DisposeObjectsBeforeLosingScope")]
internal static bool VerifyPassword(string suppliedUserName,
string suppliedPassword)
{
bool passwordMatch = false;
// Get the salt and pwd from the database based on the user name.
// See "How To: Use DPAPI (Machine Store) from ASP.NET," "How To:
// Use DPAPI (User Store) from Enterprise Services," and "How To:
// Create a DPAPI Library" on MSDN for more information about
// how to use DPAPI to securely store connection strings.
using (SqlConnection conn = new SqlConnection(
"Server=SERVERNAME\MSSQLSERVER;" +
"Integrated Security=SSPI;" +
"database=UserAccounts"))
{
SqlCommand cmd = new SqlCommand("LookupUser", conn);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter sqlParam = cmd.Parameters.Add("@userName",
SqlDbType.VarChar,
255);
sqlParam.Value = suppliedUserName;
try
{
conn.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
reader.Read(); // Advance to the one and only row
// Return output parameters from returned data stream
string dbPasswordHash = reader.GetString(0);
string salt = reader.GetString(1);
// Now take the salt and the password entered by the user
// and concatenate them together.
string passwordAndSalt = String.Concat(suppliedPassword, salt);
// Now hash them
string hashedPasswordAndSalt =
FormsAuthentication.HashPasswordForStoringInConfigFile(
passwordAndSalt, "SHA1");
// Now verify them. Returns true if they are equal
passwordMatch = hashedPasswordAndSalt.Equals(dbPasswordHash);
}
}
catch (Exception ex)
{
throw new Exception(string.Format(CultureInfo.InvariantCulture,
CustomSecurity.VerifyUserException + ex.Message));
}
}
return passwordMatch;
}
// Method to verify that the user name does not contain any
// illegal characters. If My Reports is enabled, illegal characters
// will invalidate the paths created in the \Users folder. Usernames
// should not contain the characters captured by this method.
[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Globalization", "CA1305:SpecifyIFormatProvider", MessageId = "System.Int32.ToString")]
internal static bool ValidateUserName(string input)
{
Regex r = new Regex(@"\A(\..*)?[^\. ](.*[^ ])?\z(?<=\A[^/;\?:@&=\+\$,\\\*<>\|""]{0," +
MaxItemPathLength.ToString() + @"}\z)",
RegexOptions.Compiled | RegexOptions.ExplicitCapture);
bool isValid = r.IsMatch(input);
return isValid;
}
//Method to get the report server url using WMI
[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2201:DoNotRaiseReservedExceptionTypes"), System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2201:DoNotRaiseReservedExceptionTypes")]
internal static string GetReportServerUrl(string machineName, string instanceName)
{
string reportServerVirtualDirectory = String.Empty;
string fullWmiNamespace = @"\\" + machineName + string.Format(wmiNamespace, instanceName);
ManagementScope scope = null;
ConnectionOptions connOptions = new ConnectionOptions();
connOptions.Authentication = AuthenticationLevel.PacketPrivacy;
//Get management scope
try
{
scope = new ManagementScope(fullWmiNamespace, connOptions);
scope.Connect();
//Get management class
ManagementPath path = new ManagementPath("MSReportServer_Instance");
ObjectGetOptions options = new ObjectGetOptions();
ManagementClass serverClass = new ManagementClass(scope, path, options);
serverClass.Get();
if (serverClass == null)
throw new Exception(string.Format(CultureInfo.InvariantCulture,
CustomSecurity.WMIClassError));
//Get instances
ManagementObjectCollection instances = serverClass.GetInstances();
foreach (ManagementObject instance in instances)
{
instance.Get();
ManagementBaseObject outParams = (ManagementBaseObject)instance.InvokeMethod("GetReportServerUrls",
null, null);
string[] appNames = (string[])outParams["ApplicationName"];
string[] urls = (string[])outParams["URLs"];
for (int i = 0; i < appNames.Length; i++)
{
if (appNames == "ReportServerWebService")
reportServerVirtualDirectory = urls;
}
if (reportServerVirtualDirectory == string.Empty)
throw new Exception(string.Format(CultureInfo.InvariantCulture,
CustomSecurity.MissingUrlReservation));
}
}
catch (Exception ex)
{
throw new Exception(string.Format(CultureInfo.InvariantCulture,
CustomSecurity.RSUrlError + ex.Message), ex);
}
return reportServerVirtualDirectory + rsAsmx;
}
}
}
-
My PDB file has some image miss-match issue. I tried to have correct file in correct places. Then error status change to
An error occurred while attempting to add the account. A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 25 - Connection string is not valid)But still cannot add users into database.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply