Error while connecting to Remote SQLEXPRESS

Today i installed SQLEXPRESS in a VM, and tried accessing it thru SQL Studio that was installed on different machine. It throws me one of those connection error. So I did two things

Go to SQL Server Configuration Manager

1. Start 'SQL Server Browser' service, if not already started.
2. Enable TCP/IP for SQLEXPRESS('Protocols for SQLEXPRESS).

Restart the SQLEXPRESS service. It worked for me:)

Roles in SQL Server

Just for my reference

http://www.techrepublic.com/article/understanding-roles-in-sql-server-security/1061781
http://msdn.microsoft.com/en-in/library/ms189121.aspx
http://msdn.microsoft.com/en-us/library/ms188659.aspx

An example on how to create Login/User in SQL Server and adding roles using t-sql below

IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N'myuser')
BEGIN
    DROP USER myuser
    DROP LOGIN myuser
END;
GO

CREATE LOGIN myuser WITH PASSWORD = '1234!@#$$#@!'
GO

Use [Database];
GO

IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'myuser')
BEGIN
    CREATE USER [myuser] FOR LOGIN [myuser]
    EXEC sp_addsrvrolemember @loginame = N'myuser', @rolename = N'sysadmin'
    EXEC sp_addrolemember N'db_owner', N'myuser'
    EXEC sp_addrolemember 'db_ddladmin', N'myuser' -- this contains create table permission
    EXEC sp_addsrvrolemember @loginame = N'myuser', @rolename = N'dbcreator'
END;
GO


In-case of domain account, we need to have below

CREATE LOGIN [domain\user] FROM WINDOWS
GO

To find out assigned permissions for an user, below t-sql can be used. It will display permissions for the logged-in user

"SELECT permission_name FROM fn_my_permissions(NULL, 'SERVER')";
"SELECT permission_name FROM fn_my_permissions(NULL, 'DATABASE')";

WCF serviceAuthorizationManager

ServiceAuthorizationManager can be used as custom authentication mechanism for WCF operations. So we can configure this in behavior tag

<behavior name="Behavior">
      <serviceAuthorization serviceAuthorizationManagerType="ClassThatImplementsServiceAuthorizationManager, Assembly" />
</behavior>

This is can code thru code like below

host = new WebServiceHost(typeof(Rest), address.Uri);
host.Authorization.ServiceAuthorizationManager=ClassThatImplementsServiceAuthorizationManager;

WCF Restful service with BASIC Authentication

Just wanted to show code for configuring BASIC authentication on REST service.

Hosting part
---------------
               WebHttpBinding binding = new WebHttpBinding();
                binding.Security.Mode = WebHttpSecurityMode.TransportCredentialOnly;
                binding.Security.Transport.ClientCredentialType = HttpClientCredentialType.Basic;
                binding.Security.Transport.Realm = "domain"; //Enter domain name

                host = new WebServiceHost(typeof(Service), address.Uri);
                host.AddServiceEndpoint(typeof(IService), binding, address.Uri);
                host.Open();

Client
--------
           WebHttpBinding binding = new WebHttpBinding();

            binding.Security.Mode = WebHttpSecurityMode.TransportCredentialOnly;
            binding.Security.Transport.ClientCredentialType = HttpClientCredentialType.Basic;
           
            using (WebChannelFactory factory = new WebChannelFactory(binding, address.Uri))
            {
                factory.Credentials.UserName.UserName = "user";
                factory.Credentials.UserName.Password = "password";

                var channel = factory.CreateChannel();

                channel.CallSomething();
            }

DateTime & TimeZoneInfo

static void Main(string[] args)
        {
            //http://stackoverflow.com/questions/1987824/c-sharp-synchronizing-different-time-zones
            //http://stackoverflow.com/questions/179940/c-sharp-convert-utc-gmt-time-to-local-time
            //http://stackoverflow.com/questions/576740/get-timezone-from-datetime
 
            // local time(in my case it is GMT + 5:30)
            var localTime = DateTime.Now;
            var localTime1 = TimeZoneInfo.ConvertTime(localTime, TimeZoneInfo.Local);
            
            //GMT(in my case it is Local - 5:30)
            var utcTime = localTime.ToUniversalTime();
            var utcTime1 = TimeZoneInfo.ConvertTimeToUtc(localTime, TimeZoneInfo.Local);
            var utcTime2 = TimeZoneInfo.ConvertTimeToUtc(localTime);
            var localTime2 = TimeZoneInfo.ConvertTimeFromUtc(utcTime2, TimeZoneInfo.Local);
 
            // If u receive this from other system, and knows it is UTC
            var receivedUtc = DateTime.SpecifyKind(utcTime, DateTimeKind.Utc);
 
            // .Parse thinks it is UTC(as it ends with Z), though it is local - it adds 5:30
            var localTimeParsedFromZ = DateTime.Parse(localTime.ToString("u"));
            
            //We say it is UTC, so when we use .ToLocalTime, it is really 11 hours added to local time
            var localTimeParsedFromZWithKind = DateTime.SpecifyKind(localTimeParsedFromZ, DateTimeKind.Utc); 
            
 
            Console.WriteLine("local time = " + localTime.ToString());
            Console.WriteLine("local time = local time 1 is " + localTime.Equals(localTime1));
            Console.WriteLine("local time = local time 2 is " + localTime.Equals(localTime2));
            Console.WriteLine("localTime.ToString(u) = " + localTime.ToString("u"));
 
            Console.WriteLine("Time Parsed from 'localTime.ToString(u)' = " + localTimeParsedFromZ.ToString());
 
            Console.WriteLine("local time = " + localTimeParsedFromZWithKind.ToLocalTime().ToString());
            
            Console.WriteLine("utc time = " + utcTime.ToString());
            Console.WriteLine("utc time = utc time 1 is " + utcTime.Equals(utcTime1));
            Console.WriteLine("utc time = utc time 2 is " + utcTime.Equals(utcTime2));
            Console.WriteLine("local time from utc = " + utcTime.ToLocalTime().ToString());
            
            Console.WriteLine("received utc = " + receivedUtc.ToString());
            Console.WriteLine("receivedUtc.ToString(u) = " + receivedUtc.ToString("u"));

            foreach (TimeZoneInfo timeZone in TimeZoneInfo.GetSystemTimeZones())
                Console.WriteLine(timeZone.Id);

            var est = TimeZoneInfo.FindSystemTimeZoneById("Eastern Standard Time");
            var pst = TimeZoneInfo.FindSystemTimeZoneById("Pacific Standard Time");
            var estLocal = TimeZoneInfo.ConvertTime(localTime, est);
            var pstLocal = TimeZoneInfo.ConvertTime(localTime, pst); 

         }

Unit tests - some tips

1. TestContext.WriteLine - If we want to add additional information in to our test result, we use this method within our Test method. To view this additional information, double the test in Test Result window

2. One thread per test - MSTest creates one thread per test. But tests are run sequentially without any order. Try below to verify

        [TestMethod]
        public void TestMethod1()
        {
            System.Threading.Thread.Sleep(1000);
            TestContext.WriteLine("{0}", System.Threading.Thread.CurrentThread.ManagedThreadId.ToString());
        }
 
        [TestMethod]
        public void TestMethod2()
        {
            System.Threading.Thread.Sleep(1000);
            TestContext.WriteLine("{0}", System.Threading.Thread.CurrentThread.ManagedThreadId.ToString());
        }
 
        [TestMethod]
        public void TestMethod3()
        {
            System.Threading.Thread.Sleep(1000);
            TestContext.WriteLine("{0}", System.Threading.Thread.CurrentThread.ManagedThreadId.ToString());
        }

3. If we have multiple cores, we can run tests parallel using this