none
Disabling Integrated Security on OdbConnection RRS feed

  • Question

  • I'm using Sql Username and Password in a connection string to query a database.  However, if I enter in the incorrect credentials (or if the user doesn't have access to the db), then it's defaulting to using Integrated Security (using the user that the current process is running as).  I need to disable this.  I don't want to use SSPI.  I've tried adding Integrated Security=no / false.  and Trusted_Connection=no / false to the connection string.  But it still defaults to the processes credentials rather than the credentials I've specified. 
    Tuesday, July 23, 2013 5:26 PM

Answers

  • Hi Lars,

    I agree with Lingaraj.

    It is because you are using wrong syntax for user id. Let's refer to OdbcConnection.ConnectionString on MSDN library. We will find the following info:

    ===============================

    An ODBC connection string has the following syntax:

    connection-string ::= empty-string[;] | attribute[;] | attribute; connection-string
    empty-string ::=
    attribute ::= attribute-keyword=attribute-value | DRIVER=[{]attribute-value[}]
    attribute-keyword ::= DSN | UID | PWD
     | driver-defined-attribute-keyword
    attribute-value ::= character-string
    driver-defined-attribute-keyword ::= identifier

    ===============================

    So UID is the user id attribute.

    Once we use "UID" and "PWD" keywords, OdbcConnection will try SQL authentication. When we have trusted_connection on, it will try sql authentication first and windows authentication then if sql authentication fails.

    Thanks,


    Alan Yao [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.


    Thursday, July 25, 2013 5:51 AM

All replies

  • Ask in the forum that covers the database driver about connection string settings. For SQL Server, visit the SQL Server Data Access forum.


    Visual C++ MVP

    Tuesday, July 23, 2013 8:42 PM
  • You can either use SQL or Windows Authentication with a single connection object. There is no built-in mechanism supported by ADO.Net to fallback from one to another.

    Can you share your connection string Skelton.

    I would suggest you to check your code if it has some code which is trying to connect using windows authentication when your SQL authentication fails.


    Lingaraj Mishra

    Tuesday, July 23, 2013 9:19 PM
  • Hi lars,

    I think the connection string is not correct,can you share your connection string.

    I suggest you to use SqlConnectionStringBuilder  classit provides a simple way to create and manage the contents of connection strings.

    Here is something more about SqlConnectionStringBuilder   http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnectionstringbuilder.aspx

    Wednesday, July 24, 2013 1:52 AM
  • Hello Lars Fiedler,

    Can you share your connectionString?

    If you do not want to use SSPI,you can write your connectionString like this:

    string connectionString = "DataSource=DataServerName;Initial Catalog=DBName;User ID=Passport;Password=Password;"

    And if you want to know more about it,click here:

    http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring.aspx

    Hope it hlep to you.


    Wednesday, July 24, 2013 2:47 AM
  • When executing the below code, the user running the process has access to the db.  But I set it to not use integratged security, set an invalid username and password, it its able to query the results fine.  I was expecting a login failure exception ....

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Data.Odbc;
    using System.Data;
    
    namespace ConsoleApplication12
    {
        class Program
        {
            static void Main(string[] args)
            {
                OdbcConnectionStringBuilder connectionStringBuilder = new OdbcConnectionStringBuilder();
    
                connectionStringBuilder["User Id"] = "UnknownUser";
                connectionStringBuilder["Pwd"] = "UnkownPassword";
                connectionStringBuilder["Database"] = "xxx";
                connectionStringBuilder["Server"] = ".";
                connectionStringBuilder["Port"] = 1433;
                connectionStringBuilder.Driver = "SQL Server";
                connectionStringBuilder.Add("Trusted_Connection", "no");
                connectionStringBuilder.Add("Integrated Security", "no");
    
    
                OdbcConnection connection = new OdbcConnection(connectionStringBuilder.ConnectionString);
    
                connection.Open();
    
                OdbcCommand cmd = new OdbcCommand("select Top 5 * from xxx", connection);
                OdbcDataReader reader = cmd.ExecuteReader();
    
                while (reader.Read())
                {
                }
            }
        }
    }


    Wednesday, July 24, 2013 1:30 PM
  • Try Below way,

    connectionStringBuilder["Uid"] = "UnknownUser";
    connectionStringBuilder["Pwd"] = "UnkownPassword";
    connectionStringBuilder["Database"] = "xxx";
    connectionStringBuilder["Server"] = ".";
    connectionStringBuilder["Port"] = 1433;
    connectionStringBuilder.Driver = "SQL Server";


    Lingaraj Mishra

    Thursday, July 25, 2013 1:07 AM
  • Hi Lars,

    I agree with Lingaraj.

    It is because you are using wrong syntax for user id. Let's refer to OdbcConnection.ConnectionString on MSDN library. We will find the following info:

    ===============================

    An ODBC connection string has the following syntax:

    connection-string ::= empty-string[;] | attribute[;] | attribute; connection-string
    empty-string ::=
    attribute ::= attribute-keyword=attribute-value | DRIVER=[{]attribute-value[}]
    attribute-keyword ::= DSN | UID | PWD
     | driver-defined-attribute-keyword
    attribute-value ::= character-string
    driver-defined-attribute-keyword ::= identifier

    ===============================

    So UID is the user id attribute.

    Once we use "UID" and "PWD" keywords, OdbcConnection will try SQL authentication. When we have trusted_connection on, it will try sql authentication first and windows authentication then if sql authentication fails.

    Thanks,


    Alan Yao [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.


    Thursday, July 25, 2013 5:51 AM