Search This Blog

Friday, August 23, 2013

Granting permission to a particular on Db schema (SQL Server)

There might be scenarios where you might move the code to QA or production and then grant access to user setup for the Application's access. Here is how you can do it as shown below

USE DBName
GO

GRANT EXECUTE ON SCHEMA :: schemaName TO App_User;

The highlighted in bold options are the inputs that will depend on the database name of your application, the schema whose objects you need to access and the user setup for application's access.

Wednesday, August 21, 2013

Querying Active Directory using LDAP by LDAPBrowser and C#

Insight into Problem

There may be projects/project work where you might have to lookup the user on Active Directory using some LDAP tool or querying the domain controller to see how the user has been setup without making any modifications as such. Adding to that, there might be scenarios where you might have to get user details from Active Directory for the purpose of impersonating the user in the application for the sake of troubleshooting.

Looking up users on LDAP using Tool

For looking up a user on Active Directory or Novell using a tool can be done easily using a tool like LdapBrowser and the way the tool binds user information can lay the foundation of a C# application which can do the same, if we need the user to be impersonated for troubleshooting purposes.

After installing the LDAP Browser and setting up the profile, please refer to link on how to do so and get the browser software, here we are not going into the details of that but a typical user search on directory will have the parameters and result screen as follows.

Search DN: DC=mycomp,DC=com; Filter: (cn=*kaushik*)

Whats important in the above screen shot is the Filter Text and the DN value, we are going to use similar parameters while doing it through C# application. Please look at another search below, where we use the user's id and also specify which properties to load in the search result. If you are using the Active Directory, then the userid is usually within the attribute "sAMAccountName" and on Novell its called uid. I think it depends on how the Domain Administrators set it up.

So the parameters I have put in are below
1. Search DN: DC=mycomp,DC=com
2. Filter: (sAMAccountName=myuid), here "myuid" is the id that you need to search on domain controller.
3. Attributes : cn,mail, sAMAccountName. (These are the attributes that I ask to be loaded in the Search Result).



In the above result screen, not only have we specified a more specific filter but also mentioned what attributes need to be leaded in the search result. Having fairly an idea of how things work, we are going to quickly move on to the next section and see how to do the same operations using C# application without using any third party tools.

Querying Active Directory using LDAP/C# application

The following method "searchUser", searches the LDAP based on 2 different inputs, one is the search text and the other is the searchby. The searchby specifies weather the search will done by user's name (cn), or user's id (sAMAccountName) or user's email (mail). Based on the searchBy parameter passed to the function, the filterText gets created by calling the getFilterText function, please look at the implementation in the next section along with the declaration of the LDAPUser class.

/// Searches input text on LDAP to get users
        /// </summary>
        /// <param name="searchText"></param>
        /// <param name="searchBy"></param>
        /// <returns></returns>
        public List<LDAPUser> searchUser(string searchText, string searchBy)
        {
            List<LDAPUser> result = new List<LDAPUser>();
            try
            {
                DirectoryEntry entry = new DirectoryEntry(_ldapUrl);
                DirectorySearcher mySearcher = new DirectorySearcher(entry);
                string[] propertiesToLoad = { "cn, mail, memberOf, sAMAccountName" };
                mySearcher.PropertiesToLoad.AddRange(propertiesToLoad);
                mySearcher.Filter = (getFilterText(searchText, searchBy));
                mySearcher.SearchScope = SearchScope.Subtree;
                mySearcher.ServerTimeLimit = new TimeSpan(0, 0, 60);
                mySearcher.ClientTimeout = new TimeSpan(0, 10, 0);

                foreach (System.DirectoryServices.SearchResult resEnt in mySearcher.FindAll())
                {

                    System.DirectoryServices.DirectoryEntry de = resEnt.GetDirectoryEntry();
                    LDAPUser entity;
                    if (de.Properties["sAMAccountName"].Value != null && de.Properties["mail"].Value != null)
                    {
                        entity = new LDAPUser
                        {
                            UserId = de.Properties["sAMAccountName"].Value.ToString(),
                            Email = de.Properties["mail"].Value.ToString(),
                            Name = de.Properties["cn"].Value.ToString(),
                            LdapBinderUserId = BinderUserId,
                            LdapBinderPassword = BinderPassword
                        };
                        result.Add(entity);
                    }

                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

Below is the implementation of the filter text that we need to pass to the DirectorySearcher and also the LDAPUser class declaration along with the LDAP Url that you will need to save/pass.

private string getFilterText(string searchText, string searchBy)
        {
            string result = string.Empty;
            try
            {
                switch (searchBy.ToLower())
                {
                    case "userid":
                        result = "(sAMAccountName=" + searchText + ")";
                        break;
                    case "email":
                        result = "(mail=" + searchText + ")";
                        break;
                    default:
                        result = "(cn=*" + searchText + "*)";
                        break;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }


The LDAP User class looks as below

public class LDAPUser
    {
        public long Id { getset; }
        public string Name { getset; }
        public string UserId { getset; }
        public string Email { getset; }
        
    }

The LDAP Url can be declared in configuration file or can be saved somewhere else. This url is required to be passed to the constructor DirectoryEntry before the search.

<add key="LDAPUrl" value="LDAP://mycomp.com:389/DC=mycomp,DC=com"/>

That is pretty much how you can search and get users from Active Directory using LDAP/ C#. I hope this helps.