none
VB.Net sql error 26 - error locating server/instance specified RRS feed

  • Question

  • Hello,

    I have an application that create DB then Tables inside Then some login Users

    Public Function CreateDB()
    		MessageBox.Show("CreateDB")
    		Dim Domain As String = Environment.MachineName.ToString
    		Dim script =
    	   <SQL>
               USE [master]
    
    /****** Object:  Database [AZFTS]    Script Date: 02/15/2020 00:15:14 ******/
    CREATE DATABASE [AZFTS] ON  PRIMARY 
    ( NAME = N'AZFTS', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\AZFTS.mdf' , SIZE = 15360KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
     LOG ON 
    ( NAME = N'AZFTS_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\AZFTS_log.ldf' , SIZE = 15040KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
    
    ALTER DATABASE [AZFTS] SET COMPATIBILITY_LEVEL = 100
    
    IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
    begin
    EXEC [AZFTSACC].[dbo].[sp_fulltext_database] @action = 'enable'
    end
    
    ALTER DATABASE [AZFTS] SET ANSI_NULL_DEFAULT OFF
    
    ALTER DATABASE [AZFTS] SET ANSI_NULLS OFF
    
    ALTER DATABASE [AZFTS] SET ANSI_PADDING OFF
    
    ALTER DATABASE [AZFTS] SET ANSI_WARNINGS OFF
    
    ALTER DATABASE [AZFTS] SET ARITHABORT OFF
    
    ALTER DATABASE [AZFTS] SET AUTO_CLOSE ON
    
    ALTER DATABASE [AZFTS] SET AUTO_CREATE_STATISTICS ON
    
    ALTER DATABASE [AZFTS] SET AUTO_SHRINK OFF
    
    ALTER DATABASE [AZFTS] SET AUTO_UPDATE_STATISTICS ON
    
    ALTER DATABASE [AZFTS] SET CURSOR_CLOSE_ON_COMMIT OFF
    
    ALTER DATABASE [AZFTS] SET CURSOR_DEFAULT  GLOBAL
    
    ALTER DATABASE [AZFTS] SET CONCAT_NULL_YIELDS_NULL OFF
    
    ALTER DATABASE [AZFTS] SET NUMERIC_ROUNDABORT OFF
    
    ALTER DATABASE [AZFTS] SET QUOTED_IDENTIFIER OFF
    
    ALTER DATABASE [AZFTS] SET RECURSIVE_TRIGGERS OFF
    
    ALTER DATABASE [AZFTS] SET  DISABLE_BROKER
    
    ALTER DATABASE [AZFTS] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
    
    ALTER DATABASE [AZFTS] SET DATE_CORRELATION_OPTIMIZATION OFF
    
    ALTER DATABASE [AZFTS] SET TRUSTWORTHY OFF
    
    ALTER DATABASE [AZFTS] SET ALLOW_SNAPSHOT_ISOLATION OFF
    
    ALTER DATABASE [AZFTS] SET PARAMETERIZATION SIMPLE
    
    ALTER DATABASE [AZFTS] SET READ_COMMITTED_SNAPSHOT OFF
    
    ALTER DATABASE [AZFTS] SET HONOR_BROKER_PRIORITY OFF
    
    ALTER DATABASE [AZFTS] SET  READ_WRITE
    
    ALTER DATABASE [AZFTS] SET RECOVERY SIMPLE
    
    ALTER DATABASE [AZFTS] SET  MULTI_USER
    
    ALTER DATABASE [AZFTS] SET PAGE_VERIFY CHECKSUM
    
    ALTER DATABASE [AZFTS] SET DB_CHAINING OFF
           </SQL>.Value
    		Dim connStr As String = "Server=" & Domain & "\SQLExpress;Database=master;Integrated Security=True;"
    		Dim conn = New SqlConnection(connStr)
    
    		Dim sCmd = New SqlCommand(script, conn)
    
    		Try
    			conn.Open()
    			sCmd.ExecuteNonQuery()
    			MsgBox("Done : ")
    		Catch ex As Exception
    			MsgBox("Error : " & ex.Message)
    		Finally
    			If conn.State = ConnectionState.Open Then
    				conn.Close()
    			End If
    		End Try
    	End Function
    Public Function CreateDBT()
    		MessageBox.Show("CreateDBT")
    		Dim Domain As String = Environment.MachineName.ToString
    		Dim script =
    	  <SQL>
              USE [AZFTS]
    
    /****** Object:  Table [dbo].[ShiftStates]    Script Date: 02/14/2020 14:31:56 ******/
    SET ANSI_NULLS ON
    
    SET QUOTED_IDENTIFIER ON
    
    SET ANSI_PADDING ON
    
    CREATE TABLE [dbo].[ShiftStates](
    	[ID] [bigint] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    	[EGPOpen] [money] NULL,
    	[USDOpen] [money] NULL,
    	[EUROpen] [money] NULL,
    	[GBPOpen] [money] NULL,
    	[EGPClose] [money] NULL,
    	[USDClose] [money] NULL,
    	[EURClose] [money] NULL,
    	[GBPClose] [money] NULL,
    	[UserName] [varchar](max) NULL,
    	[MachineName] [varchar](max) NULL,
    	[DateOn] [date] NULL,
    	[TimeOn] [time](7) NULL,
    	[DateOff] [date] NULL,
    	[TimeOff] [time](7) NULL,
    	[State] [varchar](max) NULL,
    	[EGP]  AS ([egpclose]-[egpopen]),
    	[USD]  AS ([USDClose]-[USDOpen]),
    	[EUR]  AS ([EURClose]-[EUROpen]),
    	[GBP]  AS ([GBPClose]-[GBPOpen]),
     CONSTRAINT [PK_ShiftStates] PRIMARY KEY CLUSTERED 
    (
    	[ID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    
    SET ANSI_PADDING OFF
          </SQL>.Value
    		Dim connStr As String = "Server=" & Domain & "\SQLExpress;Database=AZFTS;Integrated Security=True;"
    		Dim conn = New SqlConnection(connStr)
    
    		Dim sCmd = New SqlCommand(script, conn)
    
    		Try
    			conn.Open()
    			sCmd.ExecuteNonQuery()
    			MsgBox("Done : ")
    		Catch ex As Exception
    			MsgBox("Error : " & ex.Message)
    		Finally
    			If conn.State = ConnectionState.Open Then
    				conn.Close()
    			End If
    		End Try
    	End Function

    but the Login user give me Error "error 26 - error locating server/instance specified" and i use the same code and conection string except for the DB i change this time from Master to "AZFTS" as it's my DB that will use to create login user in

    Public Function adduser2()
    		MessageBox.Show("adduser2")
    		Dim username As String = Environment.UserName.ToString
    		Dim Domain As String = Environment.MachineName.ToString
    		Dim domainuser As String = "" & Domain & "\" & username & ""
    		Dim databaseName As String = "AZFTS"
    		Dim login As String = "AZFTSUSER"
    		Dim password As String = "123456789"
    		Dim passwordsa As String = "123456789123"
    		Dim script =
    		<SQL>
                CREATE LOGIN [<%= login %>] WITH PASSWORD=N'<%= password %>', DEFAULT_DATABASE=[<%= databaseName %>]
            ALTER LOGIN [<%= domainuser %>] DISABLE
                EXEC sp_password NULL, '<%= passwordsa %>', 'sa'
                USE [<%= databaseName %>]
                CREATE USER [<%= login %>] FOR LOGIN [<%= login %>]
                ALTER USER [<%= login %>] WITH DEFAULT_SCHEMA=[dbo]
                 EXECUTE sp_addrolemember db_datareader, 'AZFTSUSER'
                EXECUTE sp_addrolemember db_datawriter, 'AZFTSUSER'
                EXECUTE sp_addrolemember db_backupoperator, 'AZFTSUSER'
            </SQL>.Value
    		Dim connStr As String = "Server=" & Domain & "\SQLExpress;Database=AZFTS;Integrated Security=True;"
    		Dim conn = New SqlConnection(connStr)
    
    		Dim sCmd = New SqlCommand(script, conn)
    
    		Try
    			conn.Open()
    			sCmd.ExecuteNonQuery()
    			MsgBox("Done : ")
    		Catch ex As Exception
    			MsgBox("Error : " & ex.Message)
    		Finally
    			If conn.State = ConnectionState.Open Then
    				conn.Close()
    			End If
    		End Try
    	End Function

    Any one have idea why first two have no problem but stop with number 3 which is "adduser2"

    Monday, March 2, 2020 12:42 AM

Answers

  • Thank you for every one who replied.

    I finally managed to find the problem, my application use Click once app and so it download the SQL server and install it automatically. the problem was that in the defaulter installation the login authorization was windows authentication so when i try to use Server user it was blocked so i needed to change this first in order to connect successfully .

    the code for that is simply.

    Dim script =
    		<SQL>
    USE [master]
                EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
                N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 2
     </SQL>.Value

    After that you will need to restart the SQL Server so the changes can take effect.

    Hope this help some one.

    • Marked as answer by AbanoubZak Thursday, March 19, 2020 4:22 AM
    Thursday, March 19, 2020 4:22 AM

All replies

  • Hi AbanoubZak,

    Thank you for posting here.

    According to your description, I have a question to confirm with you.

    Could you provide more details about your exception with which line of the code thrown the exception?

    Besides, I find two related references about the exception.

    1. Unable to connect to SQL Express "Error: 26-Error Locating Server/Instance Specified)
    2. SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified

    Hope the suggestions in them could be helpful.

    Best Regards,

    Xingyu Zhao


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, March 2, 2020 10:05 AM
    Moderator
  • Hi AbanoubZak,

    Thank you for posting here.

    According to your description, I have a question to confirm with you.

    Could you provide more details about your exception with which line of the code thrown the exception?

    Besides, I find two related references about the exception.

    1. Unable to connect to SQL Express "Error: 26-Error Locating Server/Instance Specified)
    2. SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified

    Hope the suggestions in them could be helpful.

    Best Regards,

    Xingyu Zhao


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Before i post here i always search first and i have come across your references but i don't believe this is the case with me as i my first two functions run very well and the 2nd function have the same connection string as the third (which have the problem BTW) so how it can run the 2nd function with no problem but the 3rd not?

    that's why i'm sure that your reference is not the case with me as if it was the first two functions wouldn't run normally(in other words if the server is not running or the name is not correct the first two wont run) 

    for clarification the first two functions is CreateDB() and CreateDBT() and the third is adduser2()

    adduser2() is the one that give error "error 26 - error locating server/instance specified"

     
    • Edited by AbanoubZak Monday, March 2, 2020 12:56 PM
    Monday, March 2, 2020 12:52 PM
  • Have you tried running the script? For instance, here is what would run on my machine.

    CREATE LOGIN [AZFTSUSER] 
    	WITH PASSWORD = N'123456789' , 
    	DEFAULT_DATABASE = [AZFTS];
    
    ALTER LOGIN [Karens-PC\Karens] DISABLE;
    
    EXEC sp_password NULL , '123456789123' , 'sa';
    USE [AZFTS];
    CREATE USER [AZFTSUSER] FOR LOGIN [AZFTSUSER];
    ALTER USER [AZFTSUSER] WITH DEFAULT_SCHEMA = [dbo];
    EXECUTE sp_addrolemember db_datareader ,'AZFTSUSER';
    EXECUTE sp_addrolemember db_datawriter ,'AZFTSUSER';
    EXECUTE sp_addrolemember db_backupoperator ,'AZFTSUSER';


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Monday, March 2, 2020 3:37 PM
    Moderator
  • Have you tried running the script? For instance, here is what would run on my machine.

    CREATE LOGIN [AZFTSUSER] 
    	WITH PASSWORD = N'123456789' , 
    	DEFAULT_DATABASE = [AZFTS];
    
    ALTER LOGIN [Karens-PC\Karens] DISABLE;
    
    EXEC sp_password NULL , '123456789123' , 'sa';
    USE [AZFTS];
    CREATE USER [AZFTSUSER] FOR LOGIN [AZFTSUSER];
    ALTER USER [AZFTSUSER] WITH DEFAULT_SCHEMA = [dbo];
    EXECUTE sp_addrolemember db_datareader ,'AZFTSUSER';
    EXECUTE sp_addrolemember db_datawriter ,'AZFTSUSER';
    EXECUTE sp_addrolemember db_backupoperator ,'AZFTSUSER';


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    i tested on my machine it work fine  but on the client machine it run the first two function without any problem but it stop at the 3rd one
    • Edited by AbanoubZak Monday, March 2, 2020 10:55 PM
    Monday, March 2, 2020 8:03 PM
  • Did you get the same error or a different error?

    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Monday, March 2, 2020 8:05 PM
    Moderator
  • Did you get the same error or a different error?

    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    the error was on the client machine in the first place.

    on my machine which have the project i didn't even face any problem with it work find and when i tested but when i publish the application using click ones the app downloaded all the needed files and frameworks including the sql server and when the app start to create the DB and the table every thing is good ones it start the process to add new user and disable the windows authentication for sql server this where i face the Error. which is (error 26 - error locating server/instance specified)

    Monday, March 2, 2020 10:54 PM
  • Hi AbanoubZak,

    Thanks for your feedback.

    In order to get more help in SQL Server, you can consider asking your question in SQL Server forums

    Thank you for your understanding.

    Best Regards,

    Xingyu Zhao



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Tuesday, March 17, 2020 6:05 AM
    Moderator
  • Thank you for every one who replied.

    I finally managed to find the problem, my application use Click once app and so it download the SQL server and install it automatically. the problem was that in the defaulter installation the login authorization was windows authentication so when i try to use Server user it was blocked so i needed to change this first in order to connect successfully .

    the code for that is simply.

    Dim script =
    		<SQL>
    USE [master]
                EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
                N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 2
     </SQL>.Value

    After that you will need to restart the SQL Server so the changes can take effect.

    Hope this help some one.

    • Marked as answer by AbanoubZak Thursday, March 19, 2020 4:22 AM
    Thursday, March 19, 2020 4:22 AM