Results 1 to 18 of 18

Thread: Connecting to an Azure SQL Instance

  1. #1

    Thread Starter
    New Member
    Join Date
    Apr 2024
    Posts
    10

    Connecting to an Azure SQL Instance

    Hi All,

    I had a working vb.net app that connected to a server and got a list of databases. When a user selected a database, it would use a new connection string and open the selected database. All worked well until we had to move to Azure SQL Instance with MFA.

    The first initial routine still works where the user gets the list of databases and authenticates with MFA. However, when he attempts to open the selected database, (because i think it's not using the first open connection), login fails for the user.

    How can i keep the first connection open (which is authenticated with MFA) to work with the second routine of the selected database and string.

    Any help appreciated.
    Code:
    Private connection2 As SqlConnection
    Private Sub ImageComboBoxDatabase_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ImageComboBoxDatabase.SelectedIndexChanged
    
            Dim listDataBases As New List(Of String)()
            server = ImageComboBoxDatabase.EditValue.ToString
            Dim connectString As String = "Data Source=" & server & " ; Authentication=Active Directory Interactive; Encrypt=True; Initial Catalog=master; User Id=" & ArcadisUser
    
            connection2 = New SqlConnection(connectString) ' Store the connection
            connection2.Open()
    
            Dim selectSQL As String = "Select name from sys.databases where name Like 'cw_%';"
    
             Using com As New SqlCommand(selectSQL, connection2)
                Using dr As SqlDataReader = com.ExecuteReader()
                    While dr.Read()
                        listDataBases.Add(dr(0).ToString())
                    End While
                End Using
            End Using
            GridLookUpEditDropDown.Properties.DataSource = listDataBases
    
    End Sub
    
    
    Public Cnstring As String
    Private connection As New SqlConnection(Cnstring)
    Private Sub SimpleButtonProjectSwitch_Click(sender As Object, e As EventArgs) Handles SimpleButtonProjectSwitch.Click
                Cnstring = "Server=" & ImageComboBoxDatabase.EditValue.ToString & "; Database=" & GridLookUpEditDropDown.EditValue.ToString
                TreeListTableAdapter.Connection.ConnectionString = Cnstring
                Me.TreeListTableAdapter.Fill(Me.Ds_TreeList.TreeList)
    End Sub
    Last edited by jmcilhinney; Apr 21st, 2024 at 09:45 AM.

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,474

    Re: Connecting to an Azure SQL Instance

    Please don't post unformatted code snippets. As you can see, code is far easier to read when formatted properly.

  3. #3
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,474

    Re: Connecting to an Azure SQL Instance

    If you want to use one connection, use one connection. You are creating two. Don't. Create one connection and use it both times. That is something that is generally not advised but, in this case, I'm not sure there's an alternative.

  4. #4

    Thread Starter
    New Member
    Join Date
    Apr 2024
    Posts
    10

    Re: Connecting to an Azure SQL Instance

    Thank you. how do i keep the one connection open. I'm unsure how to do this

  5. #5

    Thread Starter
    New Member
    Join Date
    Apr 2024
    Posts
    10

    Re: Connecting to an Azure SQL Instance

    Anyone have an idea ?

  6. #6
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,569

    Re: Connecting to an Azure SQL Instance

    If you want to keep a connection open, then simply don't close it. If you don't close it, it's still open. That said, if the connection object goes out of scope, it will close. So you'll need to have it at a level where you can access it from where ever you need to.


    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  7. #7

    Thread Starter
    New Member
    Join Date
    Apr 2024
    Posts
    10

    Re: Connecting to an Azure SQL Instance

    thank you. how can i keep it open or use it for the second routine ?


    Code:
    Public Cnstring As String
    Private connection As New SqlConnection(Cnstring)
    Private Sub SimpleButtonProjectSwitch_Click(sender As Object, e As EventArgs) Handles SimpleButtonProjectSwitch.Click
                Cnstring = "Server=" & ImageComboBoxDatabase.EditValue.ToString & "; Database=" & GridLookUpEditDropDown.EditValue.ToString
                TreeListTableAdapter.Connection.ConnectionString = Cnstring
                Me.TreeListTableAdapter.Fill(Me.Ds_TreeList.TreeList)
    End Sub

  8. #8
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,474

    Re: Connecting to an Azure SQL Instance

    You're already not closing the database connections. The problem is that you're also creating new ones and just letting the old ones disappear off into the ether. Don't. Just create a single connection object and assign it to a single field. Open it when you need to open it and then don't close it until you're all done. Use the same field each time you want to use that same connection.

  9. #9

    Thread Starter
    New Member
    Join Date
    Apr 2024
    Posts
    10

    Re: Connecting to an Azure SQL Instance

    But how do i continue with the first connection that stays open ? Sorry, not proficient with vb.net

  10. #10
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,474

    Re: Connecting to an Azure SQL Instance

    You use it the second time the same way you use it the first time. What you're asking is like how do I take a second step in the same shoes. Just do what you did the first time. Create a connection, open it use it, use it again. Stop trying to make this hard when it's simple. If you know how to use a connection once, you know how to use it as many times as you like.

  11. #11

    Thread Starter
    New Member
    Join Date
    Apr 2024
    Posts
    10

    Re: Connecting to an Azure SQL Instance

    can i have an example please ? The problem is the first connection authenticates with MFA and i am unable to keep this connection open for the second routine
    Last edited by Sullman; Apr 23rd, 2024 at 08:20 AM.

  12. #12

    Thread Starter
    New Member
    Join Date
    Apr 2024
    Posts
    10

    Re: Connecting to an Azure SQL Instance

    bump

  13. #13
    PowerPoster PlausiblyDamp's Avatar
    Join Date
    Dec 2016
    Location
    Pontypool, Wales
    Posts
    2,547

    Re: Connecting to an Azure SQL Instance

    Quote Originally Posted by Sullman View Post
    can i have an example please ? The problem is the first connection authenticates with MFA and i am unable to keep this connection open for the second routine
    Why are you unable to keep it open? Is there something forcing you to close it?

  14. #14

    Thread Starter
    New Member
    Join Date
    Apr 2024
    Posts
    10

    Re: Connecting to an Azure SQL Instance

    The Connection stays open but the user must then select another database and from that selection a connection string is built but i am unsure how to continue the connection to achieve this

    Code:
    Public Cnstring As String
    Private connection As New SqlConnection(Cnstring)
    Private Sub SimpleButtonProjectSwitch_Click(sender As Object, e As EventArgs) Handles SimpleButtonProjectSwitch.Click
                Cnstring = "Server=" & ImageComboBoxDatabase.EditValue.ToString & "; Database=" & GridLookUpEditDropDown.EditValue.ToString
                TreeListTableAdapter.Connection.ConnectionString = Cnstring
                Me.TreeListTableAdapter.Fill(Me.Ds_TreeList.TreeList)
    End Sub

  15. #15
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,474

    Re: Connecting to an Azure SQL Instance

    I didn't initially pick up on the fact that you were connecting to the master database first and then a different database. The connection object has a ChangeDatabase method or the like, so you should be able to call that without authenticating again. There's no need for another connection object.

  16. #16

    Thread Starter
    New Member
    Join Date
    Apr 2024
    Posts
    10

    Re: Connecting to an Azure SQL Instance

    Thanks J, will research ChangeDatabase method

  17. #17

    Thread Starter
    New Member
    Join Date
    Apr 2024
    Posts
    10

    Re: Connecting to an Azure SQL Instance

    No luck i'm afraid. it still fails to fill the adapter.fill

  18. #18
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,474

    Re: Connecting to an Azure SQL Instance

    Show us the actual code you're executing and tell us exactly what happens.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width