SQL Remote Connection and upload

[Appok]

Hi all

I have been creating a VB script that does a computer inventory and uploads it to a SQL Database called Inventory and the Table is called inventory. I;m using a free version of SQL 2012 and i setup a account which i can use to log into the database and pull data off the server and display it on my Personal Microsoft excel document.

But i'd like to set it up for me to run my script from my Personal PC and upload the info to its Table

my server name is ADAM-SERVER\SQLEXPRESS and created a password and login

I'm very new to SQL and im learning as i go

 

[Appok]

mConnection.Open = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=Inventory;Data Source=ADAM-SERVER\SQLEXPRESS; UID=Adam; PWD=qazw"

this is my connection string, i dont know if its the right way for settingup a SQL Login Connection

 

[gmmastros]

You'll need to make sure your database engine is set up for SQL Authentication. Open SQL Server Management Studio, Right click on the server node. Click Properties. Click Security. Make sure it says "SQL Server and Windows Authentication"

Next, make sure you have a login to the database. Right click Security, click new login, give it a login name. Set authentication to "SQL Server authentication". Type a password, and uncheck "Enforce Password Policy".

Next, make sure the login has access to the Inventory database. Expand your database, right click security, click add new user. Type the username and login. You should set the default database to "Master". Click Membership and select db_owner.

Finally, remove "Integrated Security=SSPI" from your connection string.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Appok]

I figured it out! im able to go to each of my PC's at home and run the script and i can see all the data they have! this is awesome!

But will this work on an outside line? im currently running it inside my own home network.

 

[gmmastros]

Running from outside your own network is possible, but more challenging. In your connection string, you can put an IP Address (or web address) followed by a comma, like this:

mConnection.Open = "Provider=SQLOLEDB.1;Persist Security Info=True;Initial Catalog=Inventory;[!]Data Source=1.2.3.4,1433;[/!] UID=Adam; PWD=qazw"

The part after the comma is the tcp port that SQL Server is configured to use. You'll need to use SQL Server Configuration Manager to determine the actual port number to use.

The problem with most home networks is that they do not have a static IP Address to use. Most home-based internet service providers will assign you a temporary ip address to use without any guarantee that it will always be the same. It may be the same for 1 day, 1 month, or even years, but there are no guarantees. There are ways to get around this limitation, but I honestly don't know much about it. You could do a google search on "Dynamic DNS" to see what is available.

The last step would be to configure your router so that it forwards traffic on the "SQL Server Port" to the computer that has the database engine installed on it. This would allow you to remotely connect to SQL Server.

Alternatively, you could set up VPN if your router supports it. Then, when you want to connect to the SQL Server, you use VPN to connect to your home network and the SQL Server database engine will be available.

I personally don't use either of these, but I understand that the VPN method is more secure. When you expose the SQL port to the internet, you are also allowing hackers to attempt to break in to your computer through the SQL Engine. If they are able to figure out a user name and password, they may be able to gain access to your entire computer and home network.

Bottom line, be very careful doing this.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Appok]

Alright thanks for your help! i'll play around with it, i did see my TCP/IP and i set a port to 1433 im pretty sure i can set a static IP from my home router.

 

[Appok]

I also have another question that has been bothering me.

Each time the script has run, it fills its gaps with null cells. If i disable allow nulls my script will not run, this is what i mean.

<a href="

http://imgur.com/NzHjl"><img

src="

http://i.imgur.com/NzHjl.jpg"

alt="" title="Hosted by imgur.com" /></a>

 

[Appok]

Sorry hit the post button instead of preview, and i dunno how to edit my posts lol

 

[Appok]

i have figured out my problem as it was not the SQL is was my script, i had too many insert statements and that was the issue.