Remote MSSQL Access

[Hymn]

I was going to write a guide, but I figured I wouldn't plagiarize someone's work. I'll explain how to use it though.

You must be registered to see links

Basically follow this guide to setup your MSSQL server so that it accepts Remote connections from TCP/IP. This way you can connect to the server from your desktop without using RDC. This allows you to add/ban accounts without going through a huge hassle, along with adding items and bounty and such.

And now for my portion of the guide, actually connecting Studio Express (installed on your computer) to your VPS based MSSQL server.

Time for a bit of information gathering. This is important, it will not work without this...

VPS IP Address:
VPS MSSQL Listening Port:
1. Execute 'SQL Server Configuration Manager' (located in 'Start -> Programs -> Microsoft SQL Server 2005 -> Configuration Tools' )
2. Locate the TCP/IP entry on the list that appears.
3. Right click TCP/IP and click Properties
4. Click the IP ADDRESSES tab at the top
5. The PORT you are looking for is under IPAll (gray) 'Dynamic Port'

6. The IP Address should be the one you used to connect to the VPS.

7. From there, install Studio Express on your computer as if you had the MSSQL Server on your computer.
8. Open up Studio Express

9. Connect to Object Manager (should pop up automatically, if not, there is a button on the tool bar for it)

10. In the Dialog box that pops up, configure it to match the following:

Thats how it should look, basically the servername string should be:
255.255.255.255\SQLEXPRESS, 9999
IP ADDRESS\SQLEXPRESS, PORT


Thats it, press connect and feel free to edit the database as your heart's content.

It took me a while to figure this out, surprisingly because I guess everyone thought it simple enough that it didn't need a guide. Either way, here you go.

 

[ThroneX]

I was going to write a guide, but I figured I wouldn't plagiarize someone's work. I'll explain how to use it though.

You must be registered to see links

Basically follow this guide to setup your MSSQL server so that it accepts Remote connections from TCP/IP. This way you can connect to the server from your desktop without using RDC. This allows you to add/ban accounts without going through a huge hassle, along with adding items and bounty and such.

And now for my portion of the guide, actually connecting Studio Express (installed on your computer) to your VPS based MSSQL server.

Time for a bit of information gathering. This is important, it will not work without this...

VPS IP Address:
VPS MSSQL Listening Port:
1. Execute 'SQL Server Configuration Manager' (located in 'Start -> Programs -> Microsoft SQL Server 2005 -> Configuration Tools' )
2. Locate the TCP/IP entry on the list that appears.
3. Right click TCP/IP and click Properties
4. Click the IP ADDRESSES tab at the top
5. The PORT you are looking for is under IPAll (gray) 'Dynamic Port'

6. The IP Address should be the one you used to connect to the VPS.

7. From there, install Studio Express on your computer as if you had the MSSQL Server on your computer.
8. Open up Studio Express

9. Connect to Object Manager (should pop up automatically, if not, there is a button on the tool bar for it)

10. In the Dialog box that pops up, configure it to match the following:

Thats how it should look, basically the servername string should be:
255.255.255.255\SQLEXPRESS, 9999
IP ADDRESS\SQLEXPRESS, PORT


Thats it, press connect and feel free to edit the database as your heart's content.

It took me a while to figure this out, surprisingly because I guess everyone thought it simple enough that it didn't need a guide. Either way, here you go.

good work.. will try it .. and it should be tut no ?
p.s. first post

 

[iceman4154]

Thank you! Works great, never took the time to think I could do that
I just use LogMeIn.com for remote access

~Iceman

 

[Exiled Hero]

Allowing remote access is a big security flaw. Allowing more than one IP to access.

 

[iceman4154]

Allowing remote access is a big security flaw. Allowing more than one IP to access.

I don't exactly agree with you, you can have local remote access with the port not forwarded, then only local machines can access it (the computers on your LAN). I do agree WAN remote access is not safe, by any means. Although, a strong password would all most stop all brute force attacks.

~Iceman

 

[Phoenix]

If you don't mind, could you post a tut on how to run 2 servers on 2 different machines? Like MatchServer + Database on 1 machine and enabling the clan server from another machine with another IP to connect to the same database. It's kinda similar. Thanks.

 

[Hymn]

If you don't mind, could you post a tut on how to run 2 servers on 2 different machines? Like MatchServer + Database on 1 machine and enabling the clan server from another machine with another IP to connect to the same database. It's kinda similar. Thanks.

I want to be able to do that too, it would be so nice. As far as I know, you'd have to setup matchserver to access the database via TCP/IP similar to how this works, and the locator of your main server would have to be able to see all of the extra servers you've added. I'll look into being able to do the first one, it might run slower though, ODBC is lightning fast compared to TCP/IP.


EDIT: I ran a local test on my remote database with matchserver, I can get the matchserver to connect via ODBC, all you have to do is change the string from...

PCNAME\SQLEXPRESS to
IP ADDRESS\SQLEXPRESS, PORT


EDIT 2: If you're worried about security of said activity, you should setup a user that isnt 'sa' for your box, to prevent them from doing anything that could potentially destroy your database. It's not that hard, there is plenty of tutorials online.

 

[Exiled Hero]

The second machine only hosts the match server. The other handles everthing else. Oh add tqe obdc under system dns, other wise you may lose connection

 

[Arcelor]

Hymm, ever heard about leaked serverfiles? Now not only you gotta know the VPS password, it's easy to get the I.P. and with leaked serverfiles, access the database, execute the following query,

UPDATE Character SET Name = 'ChuckNorris', Level = *** XP = -999999. Clan = Weiners

and duck up the Ducking loser server.

 

[Hymn]

Hymm, ever heard about leaked serverfiles? Now not only you gotta know the VPS password, it's easy to get the I.P. and with leaked serverfiles, access the database, execute the following query,




and duck up the Ducking loser server.

Again, you can just setup a new user for the database. That means they need to know the following...

IP
Port
User Name
Password

It would be easier to hack into the vps via RDC brute force.

---------- Post added at 09:31 AM ---------- Previous post was at 09:30 AM ----------

The second machine only hosts the match server. The other handles everthing else. Oh add tqe obdc under system dns, other wise you may lose connection

Yep, it really is too easy actually.

 

[Arcelor]

Pointed out a 'small' problem. It's up to the peeps if they still want to use it .

 

[Hymn]

Pointed out a 'small' problem. It's up to the peeps if they still want to use it .

Its more secure than hosting a PHP based admin panel on the VPS itself. xampp is a highly insecure platform.

 

[Levi_Gz]

sorry to bring old thread up(apologizes)

but does remote mssql connection accept also accept .php script connections?

i.e to host website and gameserver separately and get data from db to site.?

or does that require hardcore coding to website scripts?