MS SQL CONNECTION ERROR ON ANOTHER COMPUTER

[tampi]

#include <ButtonConstants.au3>
#include <EditConstants.au3>
#include <GUIConstantsEx.au3>
#include <WindowsConstants.au3>
#include <ADO.au3>

#Region ### START Koda GUI section ### Form=
$Form1 = GUICreate("Form1", 384, 168, 192, 124)
$connect_ = GUICtrlCreateButton("Connect", 144, 112, 91, 33)
GUISetState(@SW_SHOW)
#EndRegion ### END Koda GUI section ###

$objErr = ObjEvent("AutoIt.Error","MyErrFunc")

While 1
	$nMsg = GUIGetMsg()
	Switch $nMsg
		Case $GUI_EVENT_CLOSE
			Exit

		Case $connect_
			ms_sql_connection()


	EndSwitch
WEnd


Func ms_sql_connection()

$sServer = '192.168.42.228, 1500' 
$sDatabase = 'usta'
$sUID = 'exampleid'
$sPWD = 'examplepass'

$DSN = 'DRIVER={SQL Server};SERVER=' & $sServer & ';DATABASE=' & $sDatabase & ';UID=' & $sUID &';PWD=' & $sPWD & ';'
$oConn = ObjCreate ("ADODB.Connection")
$oConn.ConnectionTimeout = 3 ; default is 15 s (must be supported by data provider DSN)
$oConn.Open($DSN)
If @error Then Exit

MsgBox( 1, "Connection", "Successfully Connected!")

EndFunc

Func MyErrFunc()
    MsgBox(48, 'COM Error', $objErr.description)
    SetError(1)
EndFunc

Hi, I have a problem. I don't know how can I solve it.

This script works very well on my computer. I am getting Successfull Connected! msgbox.

But it's not working on my friends computer. He is getting this error :

[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.

What should I do?

Where is the problem ?

[mLipok]

1 hour ago, tampi said:

Where is the problem ?

Connection string

Or

Firewall

Or

MS SQL configuration

[robertocm]

 

;$sServer = '192.168.42.228, 1500'
;try this:
$sServer = '192.168.42.228'

;or use the server name as from the original example by Zedna, Aug 6, 2020
;https://www.autoitscript.com/forum/topic/203542-ms-sql-connection/?do=findComment&comment=1461607
;$sServer = 'server1'

;or try changing connection string:
$DSN = 'SQLOLEDB;Data Source=' & $sServer & ';Initial Catalog=' & $sDatabase & ';User Id=' & $sUID &';Password=' & $sPWD & ';'

See also:

List all installed OLE DB providers: https://www.autoitscript.com/wiki/ADO_Tools

A possible problem of your script is keeping open connections in presence of errors because of this:

If @error Then Exit

Better always checking for open connections on exit. This would be my version of your example:

expandcollapse popup

#include <ButtonConstants.au3>
#include <EditConstants.au3>
#include <GUIConstantsEx.au3>
#include <WindowsConstants.au3>

Opt("MustDeclareVars", 1)
Opt("TrayIconDebug", 1)
OnAutoItExitRegister("OnAutoItExit")

Global $nMsg
Global $oConn, $sSQL
Global $sServer = '192.168.42.228'
Global $sDatabase = 'tempdb'
Global $sUID = 'sa'
Global $sPWD = 'examplepass'

#===== ADODB =====
;Help: COM Error Handling
;_ErrADODB From spudw2k
;https://www.autoitscript.com/forum/topic/105875-adodb-example/
Global $errADODB = ObjEvent("AutoIt.Error","_ErrADODB")

Global Const $iCursorType = 3 ;0 adOpenForwardOnly, 3 adOpenStatic
Global Const $iLockType = 3 ;1 adLockReadOnly, 3 adLockOptimistic
Global Const $iOptions = 1 ; Options, 1 Evaluates as a textual definition of a command or stored procedure call ; 2 adCmdTable
$oConn = ObjCreate("ADODB.Connection") ; Create a connection object

;~ ;https://accessexperts.com/blog/2011/03/24/sql-server-connections-strings-for-microsoft-access/
;~ ;You should bypass the ODBC layer altogether when connecting to SQL Sever by using a connection string similar to this one in your code:
;~ ;stConnect = "Provider=SQLOLEDB;Data Source=...
;~ ;Or if you’re using native client:
;~ ;stConnect = "Provider=SQLNCLI10;Data Source=...
Global $sADOConnectionString = 'Provider=SQLOLEDB;Data Source=' & $sServer & ';Initial Catalog=' & $sDatabase & ';User Id=' & $sUID &';Password=' & $sPWD & ';'

;https://www.w3schools.com/asp/prop_rs_cursorlocation.asp
;A Recordset object inherits this setting from the associated Connection object.
;This property is read-only on an open Recordset object, and read/write on a Connection object or on a closed Recordset object.
$oConn.CursorLocation = 2 ;2 adUseServer, 3 adUseClient
$oConn.CommandTimeout = 10

;https://stackoverflow.com/questions/31941487/open-adodb-connection-to-excel-file-in-read-only-mode
;https://www.w3schools.com/asp/prop_rec_mode.asp
$oConn.Mode = 1 ;Read-only

#Region ### START Koda GUI section ### Form=
Global $Form1 = GUICreate("Form1", 384, 168, 192, 124)
Global $connect_ = GUICtrlCreateButton("Connect", 144, 112, 91, 33)
GUISetState(@SW_SHOW)
#EndRegion ### END Koda GUI section ###

While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
        Case $GUI_EVENT_CLOSE
            Exit

        Case $connect_
            ms_sql_connection()
    EndSwitch
WEnd

Func ms_sql_connection()
    $oConn.Open($sADOConnectionString) ; Open the connection
    MsgBox(1, "Connection String", $oConn.ConnectionString)

    ;Zedna, Aug 7, 2020
    ;https://www.autoitscript.com/forum/topic/203542-ms-sql-connection/?do=findComment&comment=1461671
    ;simple select returns 1 value (1 column and 1 row: count() max() TOP 1)
    $sSQL = "SELECT 'Version: ' + @@VERSION + CHAR(13) + 'Language: ' + @@LANGUAGE + CHAR(13) + 'ServerName: ' + @@SERVERNAME;"
    MsgBox(0, "", $oConn.Execute($sSQL).Fields(0).Value)

    $oConn.Close ;Close the connection
    $oConn = 0  ;Release the connection object
EndFunc

Func _ErrADODB()
   Msgbox(0,"ADODB COM Error","We intercepted a COM Error !"      & @CRLF  & @CRLF & _
       "err.description is: "    & @TAB & $errADODB.description    & @CRLF & _
       "err.windescription:"     & @TAB & $errADODB.windescription & @CRLF & _
       "err.number is: "         & @TAB & hex($errADODB.number,8)  & @CRLF & _
       "err.lastdllerror is: "   & @TAB & $errADODB.lastdllerror   & @CRLF & _
       "err.scriptline is: "     & @TAB & $errADODB.scriptline     & @CRLF & _
       "err.source is: "         & @TAB & $errADODB.source         & @CRLF & _
       "err.helpfile is: "       & @TAB & $errADODB.helpfile       & @CRLF & _
       "err.helpcontext is: "    & @TAB & $errADODB.helpcontext, 5)

   Local $err = $errADODB.number
   If $err = 0 Then $err = -1

   Local $sFilePath = @DesktopDir &  "\error.txt"
   ;Open the file for write access.
   Local $hFileOpen = FileOpen($sFilePath, 2)
   ;If $hFileOpen = -1 Then
     ;MsgBox(0, "", "An error occurred when reading/writing the file.")
   ;EndIf

   FileWrite($hFileOpen, "ADODB COM Error" & Chr(1) & _
          "err.description is: "    & @TAB & $errADODB.description    & Chr(1) & _
          "err.windescription:"     & @TAB & $errADODB.windescription & Chr(1) & _
          "err.number is: "         & @TAB & hex($errADODB.number,8)  & Chr(1) & _
          "err.lastdllerror is: "   & @TAB & $errADODB.lastdllerror   & Chr(1) & _
          "err.scriptline is: "     & @TAB & $errADODB.scriptline     & Chr(1) & _
          "err.source is: "         & @TAB & $errADODB.source         & Chr(1) & _
          "err.helpfile is: "       & @TAB & $errADODB.helpfile       & Chr(1) & _
          "err.helpcontext is: "    & @TAB & $errADODB.helpcontext _
         )

   ;Close the handle returned by FileOpen.
   FileClose($hFileOpen)

   $oConn.Close
   $oConn = 0

   Exit
EndFunc

Func OnAutoItExit()
   If IsObj($oConn) Then
      If $oConn.State > 0 Then $oConn.Close ;adStateOpen Close the connection
      $oConn = 0 ; Release the connection object
   EndIf
EndFunc

 

Edited November 5, 2021 by robertocm

[Leendert-Jan]

In case your MS SQL Server is running on your own computer, you should configure MS SQL Server to allow remote connections. This is disabled by default.

First, download and install SQL Server Management Studio from here: https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver15. You have to click the first link in the article:

 

Once it is installed, open it and log in to your SQL server.

Right click on your server () and click Properties --> Connections --> Allow Remote Connections.

Next, open SQL Server Configuration Manager  and enable TCP/IP if you have not done that already.

Restart your SQL Server.

Make the firewall is configured correctly, so it does not block the port you are using.

And lastly, I am assuming your friend is on the same network as you, otherwise you probably have to configure port fowarding on your router.

 

More info:

Allowing remote connections: https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-remote-access-server-configuration-option?view=sql-server-ver15

Enable TCP/IP: https://www.habaneroconsulting.com/stories/insights/2015/tcpip-is-disabled-by-default-in-microsoft-sql-server-2014

 

Edited November 9, 2021 by Leendert-Jan
Added more info

[tampi]

Yes, this was my problem. @Leendert-Jan

I don't have router. I am using mobile phone network. I searched a little but it seems I can't port forwarding on mobile networks.

I saw this : https://azure.microsoft.com/en-us/free/students/

They giving 100$ for 1 year so I bought SQL Server(4.99$ for month).

I solved my problem that way.

Thanks for everyone!

[chkrishna]

Hi Did you get the solution for this i did not understand what others has replied for this can u please help me

[Andreik]

Open a new thread and describe your problem.