locked
Authentication method RRS feed

  • Question

  • i administer sql server 2005 instance. The instance is configured to use the named pipes network communication protocol.

    I plan to upgrade the instance to sql server 2008.

    I need to ensure that the upgrade instance can continue to use the named pipes network communication protocol. I also need ensure that the server uses the most secure authentication method available.

    Which of these should i used?

     

    A. NTLM authentication

    B. Kerberos authentication

    C. SQL server authentication

    D. Mixed-mode authentication
    get it right always
    Monday, May 9, 2011 11:37 AM

Answers

  • Hi basit59,

    You should make sence about the SQL Server Authentication Mode and the Network Authentication Protocol.

    There are two modes in SQL Server authentication: Windows Authentication mode (enables Windows Authentication and disables SQL Server Authentication) and Mixed Mode Authentication (enables both Windows Authentication and SQL Server Authentication). When possible, use Windows Authentication for security consideration. I recommend you take a look at Choosing an Authentication Mode.

    Both NTLM and Kerberos are the Network Authentication Protocols. Each of them provides a mechanism for authentication between a client and a server or between one server and another server.  As Anith mentioned, choosing which protocol to use is not the range of the upgrade. It can be specified while make a connection between client and server or server and server. Kerberos can be more secure, flexible and efficient than NTLM and can only available in domain environment. Please take a look at Understanding Kerberos and NTLM authentication in SQL Server Connections.


    Best Regards,
    Stephanie Lv

    • Marked as answer by Stephanie Lv Tuesday, May 17, 2011 10:30 AM
    Wednesday, May 11, 2011 2:44 AM

All replies

  • i administer sql server 2005 instance. The instance is configured to use the named pipes network communication protocol.

    I plan to upgrade the instance to sql server 2008.

    I need to ensure that the upgrade instance can continue to use the named pipes network communication protocol. I also need ensure that the server uses the most secure authentication method available.

    Which of these should i used?

     

    A. NTLM authentication

    B. Kerberos authentication

    C. SQL server authentication

    D. Mixed-mode authentication


    get it right always
    • Merged by Stephanie Lv Wednesday, May 11, 2011 2:46 AM duplicate thread
    Monday, May 9, 2011 11:30 AM
  • Without knowing how your applications work, I'd aim for Windows Only and Kerberos authentication.  Here's some links you might find useful to read up on...

    Kerberos Authentication and SQL Server: http://msdn.microsoft.com/en-us/library/cc280744.aspx

    Brian Kelly's blog on configuring Kerberos Authentication: http://www.sqlservercentral.com/articles/Security/65169/

    Choosing an authentication mode: http://msdn.microsoft.com/en-us/library/ms144284.aspx


    Regars, Andrew

     

     

     

    Monday, May 9, 2011 12:45 PM
  • Just a generic "most secure" answer would be kerberos but that is not how you should plan for an upgrade. Review all the applications and the users who will be authenticated to the server. For instance, if you have applications or reports that uses an ODBC with an SQL login using Windows authentication will be a wrong approach. Do a thorough requirement analysis and then identify the most feasibly authentication method.
    Monday, May 9, 2011 2:32 PM
  • Hi basit59,

    You should make sence about the SQL Server Authentication Mode and the Network Authentication Protocol.

    There are two modes in SQL Server authentication: Windows Authentication mode (enables Windows Authentication and disables SQL Server Authentication) and Mixed Mode Authentication (enables both Windows Authentication and SQL Server Authentication). When possible, use Windows Authentication for security consideration. I recommend you take a look at Choosing an Authentication Mode.

    Both NTLM and Kerberos are the Network Authentication Protocols. Each of them provides a mechanism for authentication between a client and a server or between one server and another server.  As Anith mentioned, choosing which protocol to use is not the range of the upgrade. It can be specified while make a connection between client and server or server and server. Kerberos can be more secure, flexible and efficient than NTLM and can only available in domain environment. Please take a look at Understanding Kerberos and NTLM authentication in SQL Server Connections.


    Best Regards,
    Stephanie Lv

    • Marked as answer by Stephanie Lv Tuesday, May 17, 2011 10:30 AM
    Wednesday, May 11, 2011 2:44 AM
  • Hi

    As far as I know, named pipes only works with NTLM authentication. So the answer should be A.

    Regards, Elvin

    Monday, May 16, 2011 6:39 AM
  • I believe the answer should be ‘B’ – Kerberos Authentication.


    According to Microsoft, beginning with SQL Server 2008, the SPN format is changed in order to support Kerberos authentication on TCP/IP, named pipes, and shared memory.

    Kerberos authentication is not available for SQL Server 2000 and SQL Server 2005 clients using named pipes.

    So the original SQL 2005 instance using named pipes with Kerberos would not work. Since the user is upgrading to SQL 2008 Server, according to MS, SQL 2008 supports using Kerberos with named pipes.

     

    As always - please let me know what you think of my answer. I don't like to give false information.

    D

    Wednesday, May 25, 2011 1:19 PM