none
SQL Server Connection Confusion!

    Question

  • Hey Guys,

    I'm a bit confused with connecting to Microsoft SQL Server. What I want done: The application will, during its startup, load the details of the SQL connection. During this process, the system will retrieve the server name, initial catalog, username and password.

    The code that I used to connect was as follows:

    Imports System.Data
    Imports System.Data.SqlClient
    
    Module modStartup
      Private conString As String = "Data Source=MY-PC;Initial Catalog=chromepos;Integrated Security=True;User Id='sa';Password='password';"
      Public sConn As New SqlConnection(conString)
    End Module
    

    I then adapted the code above to use the variables stored during the system startup:

    Imports System.Data
    Imports System.Data.SqlClient
    
    Module modStartup
      Private conString As String = "Data Source='" & frmMain.sql_Server & "';Initial Catalog='" & frmMain.sql_Catalog & "';Integrated Security=True;User Id='sa';Password='password';"
      Public sConn As New SqlConnection(conString)
    End Module
    

    When returning the 'conString' value at any given time, the string reads 100% correct. However, when I issue a simple SQL command using the above connection results in a failure. The error indicates that there are no columns/tables with the names specified (because the connection to SQL cannot be completed/is not available). When I change the string back to the first one and debug the application, all is 100%. When I perform a simple sConn.Open() and sConn.Close(), no errors results.

    Any explanation on this by any chance?

    Monday, April 18, 2011 3:33 PM

Answers

  • This doesn't seems right

    wrap the code in try catch, do you get any exception when you use dynamic/variable connection string...

     

     

    Try
     'your connection code 
    
    
    Catch ex As SqlException
    	MessageBox.show(ex.toString())
    
    End Try
    
    
       


    also, your connection string is not right....

     

    if you want to UserID and password in the connection string..you need to set Integrated Security =False and you do not want to use sa account?


    i.e.

    Private conString As String = "Data Source=MY-PC;Initial Catalog=chromepos;Integrated Security=False;User Id='sa';Password='password';"

    OR

    Private conString As String = "Data Source=MY-PC;Initial Catalog=chromepos;Integrated Security=True;"


    • Marked as answer by KJian_ Wednesday, April 27, 2011 2:26 AM
    Monday, April 18, 2011 5:41 PM
  • A few points:

    • You can either use Integrated Security=SSPI (or True) OR a User ID and Password. When using SSPI authentication, the current user's credentials are used to gain access to the server instance. In this case, the last keyword (User ID) wins.
    • NEVER (as in never) write an application that uses the SA account. This account is for the DBA to use and then only under special circumstances.
    • Generally, when connecting to SQL Server you need to address not only the server hosting SQL server but the named instance as well. Let's assume you're using the default instance in this case.
    • Once the connection is open, the user credentials are hidden from the ConnectionString.

    Yes, be sure to wrape these operations in a Try/Catch block.


    __________________________________________________________________
    William Vaughn
    Mentor, Consultant, Trainer, MVP
    http://betav.com
    http://betav.com/blog/billva
    http://www.hitchhikerguides.net

    “Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)”

    Please click the Mark as Answer button if a post solves your problem!

    • Marked as answer by KJian_ Wednesday, April 27, 2011 2:26 AM
    Monday, April 18, 2011 6:57 PM
    Moderator
  • I think your problem is a simple typo - compare character by character two strings above and you'll notice that the second chunk of code produces data source in quotes. It is not meant to be in quotes (just like the first option).
    • Marked as answer by KJian_ Wednesday, April 27, 2011 2:26 AM
    Thursday, April 21, 2011 3:29 PM
    Moderator

All replies

  • This doesn't seems right

    wrap the code in try catch, do you get any exception when you use dynamic/variable connection string...

     

     

    Try
     'your connection code 
    
    
    Catch ex As SqlException
    	MessageBox.show(ex.toString())
    
    End Try
    
    
       


    also, your connection string is not right....

     

    if you want to UserID and password in the connection string..you need to set Integrated Security =False and you do not want to use sa account?


    i.e.

    Private conString As String = "Data Source=MY-PC;Initial Catalog=chromepos;Integrated Security=False;User Id='sa';Password='password';"

    OR

    Private conString As String = "Data Source=MY-PC;Initial Catalog=chromepos;Integrated Security=True;"


    • Marked as answer by KJian_ Wednesday, April 27, 2011 2:26 AM
    Monday, April 18, 2011 5:41 PM
  • A few points:

    • You can either use Integrated Security=SSPI (or True) OR a User ID and Password. When using SSPI authentication, the current user's credentials are used to gain access to the server instance. In this case, the last keyword (User ID) wins.
    • NEVER (as in never) write an application that uses the SA account. This account is for the DBA to use and then only under special circumstances.
    • Generally, when connecting to SQL Server you need to address not only the server hosting SQL server but the named instance as well. Let's assume you're using the default instance in this case.
    • Once the connection is open, the user credentials are hidden from the ConnectionString.

    Yes, be sure to wrape these operations in a Try/Catch block.


    __________________________________________________________________
    William Vaughn
    Mentor, Consultant, Trainer, MVP
    http://betav.com
    http://betav.com/blog/billva
    http://www.hitchhikerguides.net

    “Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)”

    Please click the Mark as Answer button if a post solves your problem!

    • Marked as answer by KJian_ Wednesday, April 27, 2011 2:26 AM
    Monday, April 18, 2011 6:57 PM
    Moderator
  • I think your problem is a simple typo - compare character by character two strings above and you'll notice that the second chunk of code produces data source in quotes. It is not meant to be in quotes (just like the first option).
    • Marked as answer by KJian_ Wednesday, April 27, 2011 2:26 AM
    Thursday, April 21, 2011 3:29 PM
    Moderator