locked
Would authentication settings on SQL server 2008 R2 make any performance difference? ASP.net RRS feed

  • Question

  • Alright this is the first method

        public static string srConnectionString = "server=localhost; database=myDB; uid=sa; pwd=myPW;";


    And this is the second method

        public static string srConnectionString = "server=localhost; database=myDB; integrated security=SSPI; persist security info=False; Trusted_Connection=Yes;";


    Are there any performance difference or any other difference between these 2 connection strings?

    This string is being used thousands of times per second on a global asp.net website : http://www.monstermmorpg.com/


    Browser based Pokemon Style MMORPG Game Developer Used asp.net 4.0 routing at it's Monsters



    Friday, December 21, 2012 2:20 PM

Answers

  • Hi,

    a. Windows authentication imposes an extra network trip (the domain controllers) compared to SQL authentication.

    b. Please, don't tell me that your production Web Site will login to the database as 'sa' !


    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

    Friday, December 21, 2012 2:31 PM
  • As Sebastian says, Windows authentication has some overhead, but that only applies to initial connection. On a busy web site with connection pooling, actual physical connection does not happen very often.

    As Sebastian says, connecting as sa is a very bad idea. Your application account should only have the rights needed to run the application, so if there is an intrusion, the damage is limited. Ideally,

    Furthermore, the code you posted indicates that you don't use parameterised queries. Now, here is something which is important both for SQL injection and performance. With parameterised queries throughout, there is no risk for SQL injection, and since query plans can be reused, the amount of compiling on the server is reduced.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, December 21, 2012 10:35 PM

All replies

  • Hi,

    a. Windows authentication imposes an extra network trip (the domain controllers) compared to SQL authentication.

    b. Please, don't tell me that your production Web Site will login to the database as 'sa' !


    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

    Friday, December 21, 2012 2:31 PM
  • Hi,

    a. Windows authentication imposes an extra network trip (the domain controllers) compared to SQL authentication.

    b. Please, don't tell me that your production Web Site will login to the database as 'sa' !


    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

    it is logging in as sa

    here my connection class

    using System;
    using System.Data.Sql;
    using System.Data.SqlClient;
    using System.Data;
    
    public static class DbConnection
    {
        public static string srConnectionString = "server=localhost; database=myDB; uid=sa; pwd=myPW;";
    
        public static DataSet db_Select_Query(string strQuery)
        {
            DataSet dSet = new DataSet();
            if (strQuery.Length < 5)
                return dSet;
            try
            {
                using (SqlConnection connection = new SqlConnection(srConnectionString))
                {
                    connection.Open();
                    using (SqlDataAdapter DA = new SqlDataAdapter(strQuery, connection))
                    {
                        DA.Fill(dSet);
                    }
                }
                return dSet;
            }
            catch
            {
                using (SqlConnection connection = new SqlConnection(srConnectionString))
                {
                    if (srConnectionString.IndexOf("select Id from tblAspErrors") != -1)
                    {
                        connection.Open();
                        strQuery = strQuery.Replace("'", "''");
    
                        using (SqlCommand command = new SqlCommand("insert into tblSqlErrors values ('" + strQuery + "')", connection))
                        {
                            command.ExecuteNonQuery();
                        }
                    }
                }
                return dSet;
            }
        }
    
        public static void db_Update_Delete_Query(string strQuery)
        {
            if (strQuery.Length < 5)
                return;
            try
            {
                using (SqlConnection connection = new SqlConnection(srConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand(strQuery, connection))
                    {
                        command.ExecuteNonQuery();
                    }
                }
            }
            catch
            {
                strQuery = strQuery.Replace("'", "''");
                using (SqlConnection connection = new SqlConnection(srConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("insert into tblSqlErrors values ('" + strQuery + "')", connection))
                    {
                        command.ExecuteNonQuery();
                    }
                }
            }
        }
    }
    


    Browser based Pokemon Style MMORPG Game Developer Used asp.net 4.0 routing at it's Monsters

    Friday, December 21, 2012 2:39 PM
  • Hi,

    A Production external web site should never login to the underlying database as "sa", that's dangerous.

    Please, take some minutes to read about "SQL injection" risks.


    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

    Friday, December 21, 2012 2:45 PM
  • Hi,

    A Production external web site should never login to the underlying database as "sa", that's dangerous.

    Please, take some minutes to read about "SQL injection" risks.


    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

    ye i know that. and i am covering every possibility.

    how else it can connect ? i am doing read, update, delete and insert

    though they are always at row level. never delete a table and recreate

    also do you have any suggestion to my connection class ?


    Browser based Pokemon Style MMORPG Game Developer Used asp.net 4.0 routing at it's Monsters

    Friday, December 21, 2012 2:53 PM
  • Hi,

    Start by creating a database login with no server roles other than public, and give him access to the production database.

    Then, create a database user based on that login with the following roles : db_datareader and db_datawriter.

    That's enough for someone performing INSERT, UPDATE, SELECT and DELETE.

    Sadly, I can't help you the programming part.


    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

    Friday, December 21, 2012 2:58 PM
  • Hello MonsterMMORPG,

    You can create a login & give a reader & writer to that login to that particular database. Following t-sql may help:

    CREATE LOGIN xyz 
    	WITH PASSWORD = 'Xyz!@#33', 
    	CHECK_POLICY = ON, 
    	CHECK_EXPIRATION = ON
    GO
    USE <database_name>
    GO
    CREATE USER xyz
    GO 
    EXEC sp_addrolemember 'db_datareader', xyz
    GO
    EXEC sp_addrolemember 'db_datawriter', xyz
    GO


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful, if a post was useful to help other user's find a solution quicker.

    Friday, December 21, 2012 3:13 PM
  • What difference will this make ? I have only 1 database and i am the only administrator and coder

    Browser based Pokemon Style MMORPG Game Developer Used asp.net 4.0 routing at it's Monsters

    Friday, December 21, 2012 3:20 PM
  • This was in response to one of the ways to avoid SQL injection & "ye i know that. and i am covering every possibility.".

    You can use 'xyz' login credential in your connection string to restrict access to your database.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful, if a post was useful to help other user's find a solution quicker.

    • Marked as answer by MonsterMMORPG Friday, December 21, 2012 4:09 PM
    • Unmarked as answer by MonsterMMORPG Friday, December 21, 2012 4:09 PM
    Friday, December 21, 2012 3:33 PM
  • This was in response to one of the ways to avoid SQL injection & "ye i know that. and i am covering every possibility.".

    You can use 'xyz' login credential in your connection string to restrict access to your database.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful, if a post was useful to help other user's find a solution quicker.

    how exactly does it prevent an injector to delete whole table ?


    Browser based Pokemon Style MMORPG Game Developer Used asp.net 4.0 routing at it's Monsters

    Friday, December 21, 2012 4:10 PM
  • As Sebastian says, Windows authentication has some overhead, but that only applies to initial connection. On a busy web site with connection pooling, actual physical connection does not happen very often.

    As Sebastian says, connecting as sa is a very bad idea. Your application account should only have the rights needed to run the application, so if there is an intrusion, the damage is limited. Ideally,

    Furthermore, the code you posted indicates that you don't use parameterised queries. Now, here is something which is important both for SQL injection and performance. With parameterised queries throughout, there is no risk for SQL injection, and since query plans can be reused, the amount of compiling on the server is reduced.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, December 21, 2012 10:35 PM