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.

Friday, April 26, 2013

Getting full names of users when using windows authentication.

One of the most common problems that we have faced with Windows Authentication is while displaying the users full name on screen after the user gets authenticated because there is no Property to get the name that comes with WindowsIdentity or there is no API in the Security.Principal namespace. The framework exposes Environment.UserName and WindowsIdentity.GetCurrent().Name to get username with domain. Instead of splitting the Name, we could have also used Environment.UserDomainName to pass the domain to the function that is getting us the full name of the user.

So here is example of how we can do that.


WindowsIdentity wi = WindowsIdentity.GetCurrent();
string[] split = wi.Name.Split(new char[] {'\\'});
string windowsLoginName = split[1];
string fullname = GetUserFullName(split[0], windowsLoginName);
                
The function to get the Full Name is below

public string GetUserFullName(string domain, string userName)
        {
            string userFullName = string.Empty;
            try
            {
                DirectoryEntry userEntry = new DirectoryEntry("WinNT://" + domain + "/" + userName + ",User");
                userFullName = (string)userEntry.Properties["fullname"].Value;
            }
            catch (Exception)
            {
                userFullName = string.Empty;
            }

            return userFullName;
        }

Tuesday, October 23, 2012

A Known Error in WCF Restful Service

I found a resolution of a commonly occurring error while working with WCF restful services and I copied the entire problem description and its resolution from this blog.

http://blogs.msdn.com/b/justinjsmith/archive/2008/02/15/enablewebscript-uritemplate-and-http-methods.aspx

The text reads as below

A little while ago I ran into an interesting set of errors that may be of interest to you. Consider the following service contract snippet:
[OperationContract]
[WebGet(UriTemplate="foobar/{value}")]
String GetData(String value);

If you add the enableWebScript behavior to an endpoint that is using the WebHttpBinding, you will see this exception when the ServiceHost starts:

System.InvalidOperationException: Endpoints using 'UriTemplate' cannot be used with 'System.ServiceModel.Description.WebScriptEnablingBehavior'.

The reason for this error is rooted in the origin of the enableWebScript behavior. One of it's design objectives was to simplify working with the ASP.NET AJAX stack (Javascript proxy, JSON messages, etc). The AJAX stack doesn't have the equivalent of the UriTempalte type. It simply puts parameters in query strings (gets) and constructs entity bodies (posts). This is the default behavior of the WCF stack when the WebGet / WebInvoke annotations do not have a value for UriTemplate. Since any value of UriTemplate would be incompatible with the ASP.NET AJAX stack, we throw when it's present.

If you want JSON messages from a contract and you want to use the UriTemplate niceness, you can change your contract to:
[OperationContract]
[WebGet(UriTemplate="foobar/{value}", ResponseFormat=WebMessageFormat.Json)]
String GetData(String value);

Then, instead of using the enableWebScript behavior, use the WebHttpBehavior. You'll lose compat with the ASP.NET AJAX client stack (and the JS proxy), but you have the URI you are looking for.

The same is true if you are using the WebInvoke attribute and any HTTP method other than POST. The AJAX client stack only knows GET and POST... 

Thursday, October 4, 2012

SQL:Finding occurance of chars/words in String

The following is a very simple logic to check the ocuurance of chars/word in a larger string. This thing is particularly important when you want to just check if the string that you are searching for occurs as a sub-string of a value in a larger string which may be a value of a column in a table.

One practicle example would be say for example you want to check if a role is there in a column of user_roles which has a comma separated value of all roles for a particular user, so here you know what you have to look for in a column of all roles.

DECLARE @LongSentence VARCHAR(MAX)
DECLARE @FindSubString VARCHAR(MAX)SET @LongSentence = 'My Super Long string with long words'SET @FindSubString = 'long'SELECT (LEN(@LongSentence) - LEN(REPLACE(@LongSentence, @FindSubString, ''))) CntReplacedChars,(
LEN(@LongSentence) - LEN(REPLACE(@LongSentence, @FindSubString, '')))/LEN(@FindSubString) CntOccuranceChars

In simple terms all it does is

Length of Long Seq (36) minus Length of Long Sentence with all 'long' replaced with blank (24) = 8 so we have 8 ocurrences of the characters

The above divided by the Length of 'long' will give you the occurrences of the word 'long' in the Long Sentence i.e 2

or 8/4 = 2


**I got the above T-SQL from this source http://blog.sqlauthority.com/2010/08/16/sql-server-finding-the-occurrence-of-character-in-string/

Wednesday, September 26, 2012

Adding System.ServiceModel.Web.dll in v4.0

While creating a WCF service library on Visual Studio 2010, I am sure many of you have faced an issue where you are not able to find the System.ServiceModel.Web.dll.

The reason being that Visual Studio by default targets the 4.0 Client Profile and System.ServiceModel.Web.dll is not present in that list of libraries to be referenced. The work around is to change the target framework to v.4.0 instead of the 4.0 Client profile.

* The same problem will not arise if you have created a WCF Service Application.


Sunday, August 19, 2012

WCF Base Address error on Windows Vista

I am sure its not only me but many of you have encountered an unusual error when you first started an instance of a WCF Host application through Visual Studio on Windows Vista, the error will look something like this below

{"HTTP could not register URL http://+:49779/WcfServiceLibraryNorthwind/NorthwindService/. Your process does not have access rights to this namespace (see http://go.microsoft.com/fwlink/?LinkId=70353 for details)."}

Since vista's security is not exactly like old windows, so the owner of an HTTP namespace (in this case the administrator) will have to delegate that ownership to another user, so the command would be something like this below


"netsh http add urlacl url=http://+:portNumber/ user=MYMACHINE\UserName"

i.e as below in my case
netsh http add urlacl url=http://+: 49779/ user=KAUSHIK-PC\Kaushik

*On success, you will get a message like below
URL reservation successfully added

** please open the command shell "As Administrator", otherwise you will get an error as below 

Url reservation add failed, Error: 5

Tuesday, October 4, 2011

WebGrid Control Issues

If you are using web grid third party control in any of your applications please make sure to use it for the exact version of ASP.NET for which it was built for. Suppose if say you have a Webgrid to be used for an ASP.NET 1.1 application and if my mistake your default Website or your virtual directory has a version of ASP.NET as 2.0, then the webgrid will throw error like the datasource or memory used to access data from grid is corrupt and that is basically because the other version wp might not have access to the webgrid datasource.

Monday, September 12, 2011

Android app on windows using Eclipse

First all please go through this blog which guides developers like me who are basically .NET developers but having an interest on building Android application using windows platform.


http://www.caffeinedi.com/2010/07/03/beginning-google-android-development-for-net-developers-part-1/

The blog post will guide anyone on how to setup the environment and run your first app, but I want to highlight some of the issues I faced and which are quiet common and resolutions/work around those issues. First of all the 4 things that needs to be setup are


1. Java Development Kit (JDK) 6 – self-explanatory; the Java runtime and development libraries.


2. Eclipse – an IDE (integrated development environment) for Java and other languages.   If you don’t have it installed, grab the Eclipse IDE for Java Developers version, which will suit the needs of this tutorial.  The Windows download doesn’t contain an installer, so extract the ZIP in an accessible location, such as D:\eclipse.


3.Android Software Development Kit (SDK) – The heart of Android development.  This provides all of the necessary tools and libraries required to build applications for the Android platform.  Extract it to an accessible location on your hard drive and run the setup executable, which will download and install a set of components.


4.Android Development Tools (ADT) Eclipse Plugin – This plugin adds Android-specific development tools and project templates to Eclipse.  Instead of a separate download, it installs within Eclipse – you can follow the instructions here to get it installed.


ISSUE 1 At step 3, when you try to run the executable to install Android SDK, then that might give errors because it might not be able to detect the JDK. Instead of cancelling the installation, just click "Previous" button and go the previous screen and then "Next" button ,it will detect it this time for sure.


ISSUE 2 To install the ADT as in Eclipse plugin, follow the steps below to first download it as a plugin
1. Start Eclipse, then select Help > Install New Software....
2. Click Add, in the top-right corner.
3.  In the Add Repository dialog that appears, enter "ADT Plugin" for the Name and the following   4. URL for the Location:

    https://dl-ssl.google.com/android/eclipse/

    Click OK

    Note: If you have trouble acquiring the plugin, try using "http" in the Location URL, instead of    "https" (https is preferred for security reasons).
    In the Available Software dialog, select the checkbox next to Developer Tools and click Next.
    In the next window, you'll see a list of the tools to be downloaded. Click Next.
    Read and accept the license agreements, then click Finish.

    Note: If you get a security warning saying that the authenticity or validity of the software can't be established, click OK.
    When the installation completes, restart Eclipse.



ISSUE 3 After creating your first Android virtual device, when you try to run the application, you can get an error like 
Emulator] PANIC: Could not open: C:\Documents and Settings\UserNAme\.android/avd/Android2.2.ini
This happens because if your Android SDK location is in any other location than C drive, the eclipse tool will not be able to locate that folder .android, so the solution would to look for the folder *.android and copy it over to the location sorted by the eclipse tool. In my case, I copied the folder .android from a location in my D: drive to C:\Documents and Settings\UserNAme\.android.
ISSUE4 While launching the new emulator, you might again get an error like
 Emulator] invalid command-line parameter: Files\Android\android-sdk\tools/emulator-arm.exe.
 Emulator] Hint: use '@foo' to launch a virtual device named 'foo'.
 Emulator] please use -help for more information



To get around this issue change the SDK location path in eclipse to D:\PROGRA~1\Android\android-sdk for a 32 bit machine or change to D:\PROGRA~2\Android\android-sdkfor a 64 bit machine if your SDK installation is on D: drive or else the drive accordingly. 


This issue is because of the fact that eclipse will not be able to SDK location if SDK location contains any spaces.

Sunday, July 31, 2011

Creating a linked Server on SQL Server

EXEC sp_addlinkedserver @server='GM', @srvproduct='',
@provider='SQLOLEDB', @datasrc='databaseIP,Port',
@catalog ='DBName'
EXEC sp_addlinkedsrvlogin 'GM', 'false', NULL, 'DBUser', 'DBPassword'

--The Query, Using the Server Database you are already in and your new linked server: GM--
SELECT
NUMBER,
DT_CREATED AS DATE_CREATED,
PRIORITY,
(SELECT COMPANY FROM GM.myDatabase.dbo.myTable WHERE ACCOUNTNO IN (SELECT TOP 1 ACCOUNTNO FROM GM.myDatabase.dbo.myTable WHERE Field1=(SELECT CUSTOMER_NUMBER FROM CUSTOMERS WHERE ID=ID_CUSTOMER))) AS COMPANY FROM MAIN_TABLE