Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Test for SQL Server Authentication Mode (for use in installer)

Unanswered Test for SQL Server Authentication Mode (for use in installer)

  • Tuesday, October 09, 2012 12:52 PM
     
     
    Sign In to Vote
    0

    Hi,

    We are building an installer that will want to install a database on a remote database server. Before we attempt anything on that server, we want to test that server to find its Authentication mode setting (it looks like we would want to use UIConnectionInfo.AuthenticationType, but on its reference page, it says never to use it). We want to then instruct the user to do something if "mixed-mode" is not set on that server (such as have it enabled).

    Is there a way we can do this?  We will not have access to the console of the database server, nor will we be able to look at the registry of the database server. Just trying to go ahead and let it fail and not be sure why it failed will only lead to a very long and political battle at the user site that we want to avoid.

    Thanks,
    Rob


    thanks, Rob Hutchison

     

All Replies

  • Tuesday, October 09, 2012 1:15 PM
    Moderator
     
     
    Sign In to Vote
    1

    Hello,

    Please see the following resources:

    http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/a14551a9-60e9-4f37-bd08-dc7e1db5cdbe

    http://www.mssqltips.com/sqlservertip/2191/how-to-check-sql-server-authentication-mode-using-t-sql-and-ssms/ (3 different ways)

    Hope this helps.

    Regards,

    Alberto Morillo
    SQLCoffee.com

     
  • Tuesday, October 09, 2012 1:30 PM
     
     
    Sign In to Vote
    0

    Hi,

    How can you run T-SQL on the machine if you haven't connected to it yet?

    thanks, Rob Hutchison

     
  • Tuesday, October 09, 2012 1:49 PM
    Moderator
     
     
    Sign In to Vote
    0

    Hello,

    If not connected to SQL Server, then the only option that I know is accessing the Windows registry. No other option to my knowledge.

    Hope this helps.

    Regards,
    Alberto Morillo
    SQLCoffee.com

     
  • Tuesday, October 09, 2012 2:12 PM
     
     
    Sign In to Vote
    0

    Hi,

    Thanks, but I don't believe you are allowed to access the registry of a remote machine.

    My problem is that I believe that this is a very normal thing that people must always need to do. I can't imagine that the "best practice" is to let the install fail and then play detective and try to figure out what caused it to fail. That is always an ugly process.

    I am hoping somebody has figured this out?

    thanks, Rob Hutchison

     
  • Tuesday, October 09, 2012 2:15 PM
    Moderator
     
     
    Sign In to Vote
    1
    The thing is in majority of installations where database deployment is required, connection parameters to database server are required to be provided by the user during setup process. 

    Regards,
    Ahmed Ibrahim
    SQL Server Setup Team
    My Blog
    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you.
    This can be beneficial to other community members reading the thread.
    View Ahmed Ibrahim's profile on LinkedIn

     
  • Tuesday, October 09, 2012 3:06 PM
     
     
    Sign In to Vote
    0

    Hi,

    ...and the installer needs to test them before using them. All too often, our users will be told that the server has "mixed-mode authentication" enabled and that the user running the installer has authority to create an account, databases and do all the other necessary things when, in reality, one of these things is not the case. It could also be a problem with firewalls or protocol settings.

    We need to have our installer test these conditions before trying to create the user or trying to create the DB so that we can give proper feedback. This will allow our user to have a clear screen shot to send to the DB admin. This will reduce a one week political blame game to a 30 minute simple support call.

    Yes, the majority of the installs are easy, but the majority of our support time is consumed by the minority of the installs where problems like this occur. Our ability to provide an overall better customer experience depends on things like an ability to create smarter installers. That is why I am asking this question and am still hoping for some advice that helps.

    thanks, Rob Hutchison

     
  • Wednesday, October 10, 2012 3:21 AM
    Moderator
     
     
    Sign In to Vote
    0

    Hi Rob,

    Base on your description, you want to use UIConnectionInfo.AuthenticationType, but couldn’t get it work. Base on my research, this API supports the SQL Server 2012 infrastructure and is not intended to be used directly from your code. Do not reference this member directly in your code. It supports the SQL Server infrastructure. Gets or sets a Int32 value that specifies whether the connection dialog should use SQL Server Authentication or Windows Authentication. For more details, please refer to this article: UIConnectionInfo.AuthenticationType Property.


    TechNet Subscriber Support

    If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.

    Best Regards,
    Ray Chen

     
  • Wednesday, October 10, 2012 11:48 AM
     
     
    Sign In to Vote
    0

    Hi,

    Right - that's what I was referring to in my original question. The same call is also available in several of the earlier editions - this is what I was referencing when I said "...but on its reference page, it says never to use it".  You just copied and pasted from the article.

    Regardless, the problem still exists.

    What I currently have to work with is: when I try a single operation, it can fail for six or seven very different reasons and I always get back the same generic error. Diagnosing each of these six or seven different reasons involves significant "detective work" that typically includes people other than the person installing the application. Some of these people are easy to access and others are not. I need to reduce this effort by providing them better information as to what needs to be fixed.

    thanks, Rob Hutchison