how to configure sql server 2000 and windows server 2000 in order to allow remote connection through vb
-
hi,
My local database (under ms sql server 2000, Enterprise Manager) currently works fine with a vb application in that I can access the database using a connection string with the ADODB VB object. My plan is to use that same database from a remote computer( Computer A, for our sake of making things clear) such that the VB application will run on Computer A and use the ms sql database in computer B, the computer where windows 2000 server installed, apart from the sql edition specidied above. I know that as far as the connection string is concerned, I will need to include the IP address and the port of computer B, through which communication will be established. The coonectionstring property of the ADODB.connection object will look something like this:
ConnectionString = "Driver={SQL Server};" & "Server=24.191.235.110;" & "Address=24.191.235.110,1433;" & "Network=DBMSSOCN;" & "Database=pubs;" & "User ID=sa;" & "Pwd=*****"
When it comes to implementing the setup supporting the success of using such connection string I’m totally ignorant. To be more specific, I am not sure whether there is anything I need to do with respect to the windows 2000 server of Compute B (other than installing it, which I did). I’m sort of fumbling on the ms sql server side. I got bits of information that lead me to open the ‘Server Network Utility’ form of MS Sql, where I enabled the ‘Names Pipes’ and the ‘TCP/IP’ protocols, and I enabled the WinSock proxy, entering into the ‘WinSock Proxy Addrees’ text box the IP address that I obtained by going to a website which tells the one entering it the IP address of the computer used when doing so. The link is :
http://oi.sfsu.edu/cgi-bin/student/whatsmyip
Al,so, in the other text box corresponding to the ‘WinSock Proxy Port’, I entered the number 1433. The ‘instance server’ value was left with the default value. The same IP address used in the ‘Server Network Utility’ form was used in the connection string above. The user name and the password in the connection string are the same ones used when the VB application successfully connected to the local database (as described above) But when the VB application is run from computer A with the connection string above and the ‘Server Network utility’ configuration, I get an error message upon VB application trying to execute the connect method of the ADODB.connect object (whose connection string is the one above).
I hope I explained my problem in a way that the readers in this discussion group can understand.
I will be greatly appreciative if anyone can help me in that matter and tell me what I need to do. I have browsed the internet for the past two day, to no avail. Anything that I do with respect to configuring the systems (ms sql 2000, and windows server 2000) is a good guess at best.
If you do get to respond, please cc me at avisemah@optonline.net
Thank you in advance
Avi
-
Did a default install of MSDE on local machine and default install of SQL 2000 on a network server. Default = accepting all of MS setting for all variables and flags
Try this snippet of code:
'
' Add to project references a reference to: Microsoft ActiveX Data Objects 2.8 Library
Dim Cnn As ADODB.Connection
Dim Cnn_string As String
Dim Ado_Cmd As ADODB.Command
Dim objMSDAC As MSDASC.DataLinks
Dim Break_Point As Integer
On Error GoTo E_Han
'
' Use this bit of code in debugging only
'
' Set a break point where I have indicated
'
Set objMSDAC = New MSDASC.DataLinks
Cnn_string = objMSDAC.PromptNew
Break_Point = 1 'Set this as your break point
'
' Using immediate window display the Cnn_String
' Cut and paste the cnn_String into your code
' Once you have done that comment out the 3 code lines above and the Dim statement ' of MSDASC
'
-
Slightly easier to bibucket49's method, but basically the same idea.
On the workstation, create a new text file and name it with a .udl extension. Then double-click on the file, and you will get the same dialog to connect to database. Setup and test your connection and save it.
Then open the .udl file in notepad (hold down shift key and right-click on the file name, then select "Open With..." and choose notepad) and you will have the connection string there to copy and paste to your code.
Mark -
hi bitbucket49,
First and foremost, I would like to thank you for taking some of your time and trying to help me in this matter.
If there is anything that I say in this e-mail with the intention to make things clear, please regard it as my failure to explain the problem in a clear manner.
About installing MSDE on the machine where the vb code is running, I need to say that my project requirements dictate that any remote machine using the ms sql database will have only a piece of vb code, along with data access components, I believe, that together will access the database remotely. To be more descriptive, I work on a point of sale project, where one central database is going to be used by multiple stores such that each store will run on its local machine the code that performs all the sales transaction, etc. The only thing that will not be installed on the local machine is the database, which, as we know, will reside in one machine. I got to a point where the database works well with a vb code when it comes to both of which residing in one machine (i.e., using local ms sql database). Basically I created a connection string that works well in conjunction with Microsoft ActiveX Data Objects 2.7 Library (added in project references). I also managed to learn from someone that this design, whether inefficient, is feasible. The actual connection string that worked for me on local machine looks like this :
dim conn as adodb.connection
ConnectionString = "Provider=sqloledb;" & _
"Data Source=AVIWIN2KSERVER;" & _
"Initial Catalog=cellular;" & _
"User Id=sa;" & _
"Password=*****"
conn.Open ConnectionString
So now I'm at the point of placing the vb code that works well locally on a remote machine, making sure that all the data access components on the remote machine are there, and changing the connection string to something close to the one I included in the first post :
ConnectionString = "Driver={SQL Server};" & "Server=24.191.235.110;" & "Address=24.191.235.110,1433;" & "Network=DBMSSOCN;" & "Database=pubs;" & "User ID=sa;" & "Pwd=*****"
This connection string was provided to me by someone who has already tested it successfuly to the extent that the vb code ran on a different machine where the sql database resided. I'm trying to do the same, and I have the hunch that there is somethinng not configured well on the machine where sql data base is, somwthing that is not even related to the so-called interaction of ssql serever and windows 2000 server softwares. I tend to think so because of another problem I detected, which I think is related to this post, probably even the root cause. I am obviously not sure whether it's a problem or how to solve it. This other problem could be described as follows : When I type http://localhost in the adrres bar of IE, IIs 5.0 (which is embedded in windows server 2000) takes me to the default website webpage. The same thing happens when I type http://127.0.0.1 My understanding, and it's not that great, is that this is done internally with the help of the IIS 5.0 server, since, after all, it's the server which gets the request in the address bar and dircets the browser to openn the default wb site. When I try to do the same from the internet, from another remote computer, by typing my computer's IP, I get an Internet Explorer error message that the page caa not be displayed. I'm not sure whether I'm making the right assumption that typing the computer's IP should open the default website, just as it happens when typing the http://localhost But if my expectation is correct, then it means that the connection INTO the machine where windows server 2000 is does not function, which, if true, can also be the reason why I can not connect to the ms sql database remotely. When I suggested above that there is an independent problem that is not really related to the interaction of the data access components, sql data base, and VB, I was taliking about that inability to connect remotely to the server. I'm pretty sure that I over use the word server because I only installed windows 2000 server, which, by default, installs IIs 5.0 upon installtion. Maybe I need to configure IIS 5.0 first, before even using sql database remotely. Maybe I'm just thginking that I have a fully operational server, and that is what you guys assumed when I initially described the problem in the first post.
I hope I did not make things motre complicated to all of you to the point of giving up on me.
once again, thank you for your time and willingness to help
Avi
-
Hi Mharr,
Thank you for the help and the suggestion you have made in your post. I just replied to bibucket49, and I suggest you read what I wrote in my reply -- it gives one a better sense of the problem in question.
I must say that I'm impressed with the technique that you have mentioned (i.e., the udl extension) I went through that process before you posted your suggustion in a different manner. I did so by going to administrative tools on a another machine in the WORKGROUP (which all the computers in my house are members of, including the one with the sql database). Then I went to datasources(odbc), where I tried to add a DSN file such that the connection corresponding to that DSN will be from one computer to another. I was successful in establishing a connection, but, to the best of my understanding, and it's not alot, it is not a connection where one remote computer in the internet connects to an IP. I don;t know the right terminology how to say that it's not an outside connection. To be more specific, when I'm prompted with the DSN configuration form, where I need to specify a server, I get a drop down list with one entry, the name of the computer where the windows server 2000 is. When I select that compuetr and test the connectin, I get a 'tested successfully ' message. But how would this success can take the form of a success to establish connection from and outside computer, where the machine name where the sql databse can not be seen, where the only thing through which a remote machine can access the sql database is by knowing the IP address? I'm sure I have some sort of fixation in my mind that does not let mefind the solution.
many thanks to you
Avi
-
The Machine name can be ALIASED with the Network Client Utility!!
* Noel -
Sorry Avi, I had misinterpreted your question. I did not realize you were trying to access SQL Server over the internet.
While you can have a direct connection to SQL Server over the internet (I think you have to make sure the ports 1433/1434 are open both ways, and I think there is a IIS setting in that may have to be done in the msadc virtual directory), I do not think that you want to do that, as it opens total control over your entire server to potential attack and loss of control. Instead, you want to setup a virtual server to handle queries for you, and pass back data. Take a look in BOL for "Accessing SQL Server Using HTTP", or here on the online version: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/xmlsql/ac_xml1_59m4.asp
Although I have not setup a system like this, I believe that accessing SQL Server over the internet is not the same as doing a ADO connection. Your connection is not persistent to be able to establish a connection, then perform a query. You basically need send a query to a proxy on the server to make the connection, execute the query, and return the result sets.
Depending on how well you abstracted your data access, it should not be too much trouble to convert your application to this method.
Mark -
hello noeld,
Thanks for your reply. I'm glad that you brought it up because I'm not really familiar with the client netweork utility, and it could be that this is where the answer to the problem lies. But before I do anything, I just wanted to ask you somethin gin that respect. If I have my ms sql database installed in one machine then I would gather that I need to allow it to communicate as aserver with other machines in which ms sql is not installed. Now, the was I understand the client network utility, it's good for other remote machines that DO have ms sql installed in them. Please correct me if I'm wrong. If I'm wrong then I should probably employ the client network utility and make sure it's configured properly. The question is where? because I'm not really sure.
many thanks
Avi
-
Hi mharr,
I tend to thin that it is me who gets to misrepresent the problem over and over, thereby confusing all thos e who have the ability to help. It it all comes down to my usage of all kind of terms that I'm not so sure about but that carry you to other directions.
When I said to connect through the internet, I must have mislead you. I don't mean connecting through the internet per se. what I mean is using the following connection string from vb. I know it worked for someone else, and it should work for me as well, provided that I don't overlook what I seem to right now.
ConnectionString = "Driver={SQL Server};" & "Server=24.191.235.110;" & "Address=24.191.235.110,1433;" & "Network=DBMSSOCN;" & "Database=pubs;" & "User ID=sa;" & "Pwd=*****"
If the link you have sent me is in the same line of achieving that goal and I can't see it, then it's probably due to the fixation in my mind . so Please let me know if that is the case, that is, whetnher what you have sent me is the solution but I just can't see it becasue I don't know enough.
You see my small realm of understanding told me that if a connection string includes anIp address in it, then the connection is through the internet. And I think that this is the reason that I mislead you, assuming that the connection I'm talking about is different than what you offered to me( the http connection). I might have wrongly inferred that this is n internet connection. In fact, as I see nit now, it must be and ODBC connection because, I believe, the connection string could be contsructed with the ODBC form from administartive tools. All that needs to be done is to insert the right IP address and make sure that what needs to work is configured properly.
About security, I think that my router is coming with firewall, or if not, then some other routesr that I can obtain in the market are embedded with firewall, and that is how I plan to access the ms sql database in the future. Someone told me about this, I ndid not figure it out by myself.
If you asked my poor knowledge but my strong hunch where the solution lies, I would tell you that it's somethin with the computer Ip addresss. I think that incoming connection need to identify the location of the server so to speak, and in that respect, I need to configure my router maybe, or some file that would tell to anyone trying to connect where to go. After all, there are 3 computers in my house vconnected to a router. Any incoming connection needs to 'land' in the righ tplace. To do that, I would dare to say, we need to hel the router arrive to the right aplication that handles the request/service. Or maybe the router does do its job -- maybe it's the server that does not listen 'properly' to service requests.
again, many thanks for your effort to think through the problem and for the prompt reply
Avi
-
Client Network Utility is to be used well ... ON THE CLIENTS !!
No need for server to be there
* Noel -
hi again and thanks for the prompt reply, but can you pl;ease elaborate and eplain about the use of this cliant netweok utility an dhow it migh fit the problem in hand.
thank you
Avi
-
Ok,
Step By Step:
1. MAke sure you can verify the IP Address of your Server !
2. From the Client try to ping it!
3. Install the client network utility on the Client (where your vb will be)
4. Open the utility Go to the Alias TAB and select New
5. On the ALIAS NAme enter a Name for your server
6. Select TCP IP protocol and on the SERVER NAME ENTER THE IP ADDRESS
7. Uncheck dynamically determine the port!
8. Use This Connection String on your APP =
"Driver={SQL Server};SERVER=ALIASNAME;DATABASE=YourDataBaseName;UID=YourUID;PWD=YourPassword;"
The advantage of this is that if you want to chage the IP address you just change it on the ALias or if you need to connect o a different SS instance again you just change the ALIAS your connection string DON'T Change!
HTH
* Noel
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply