Remote SQL database connection

Question

0.00/5 (No votes)

See more:

Good day to you all. I am trying to connect to a remote sqlserver database through a windows form application. But the only alternative i keep getting the connection string is the following which i wish to know how secure it:

"Data Source=190.190.168.100,1433;Network Library=DBMSSOCN;Initial catalog=myDataBase;User ID=myUsername;Password=myPassword;"

in the place of user name, I used the "sa" account which ignited my doubt about the safety of the string.

The remote server is running on windows server 2012 and the db is sql2014.

Any help with this will be appreciated.

Thanks in advance
Frank.

What I have tried:

I use the following connection string which when running within the same domain works well, but failed with other network domain over the internet.

"Data Source=NEPTUNSERVER\NTSERVER;Initial Catalog=ITEM_MGR; integrated security =True"

Posted 17-Aug-16 4:59am

Frankie-10589807

Updated 26-Aug-16 2:00am

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

FranzBe · Answer 1 · 2016-08-17T21:48:00

As your SQL Server is running in mixed mode and you want to login from some other domain, you should stay with the SQL Server authentication mode approach.

You are right to hesitate using "sa". Create a user for your application in SQL Server (get help from a database admin if you are not allowed to do so).
Grant this user not more than the rights needed by your application.
You may generate the required sql commands with a script

SQL

 IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname = N'yourUserName')
BEGIN
    CREATE LOGIN [yourUserName] WITH PASSWORD=N'yourPassword'
   ,DEFAULT_DATABASE=[yourDataBaseName]
   ,CHECK_EXPIRATION=OFF
   ,CHECK_POLICY=OFF
END


IF NOT EXISTS (
		SELECT *
		FROM dbo.sysusers
		WHERE NAME = N'yourUserName'
			AND uid < 16382
		)
	EXEC sp_grantdbaccess N'yourUserName'	,N'yourUserName'


 
SELECT 'GRANT SELECT,INSERT,UPDATE,DELETE ON ' + a.table_name + ' TO yourUserName'
FROM (
	SELECT DISTINCT table_name
	FROM information_schema.columns
	WHERE table_name LIKE '%yourSearchToken%'
	) a

-- check, edit the resulting list and execute it

Subodh_Bhatkar · Answer 2 · 2016-08-26T02:00:00

Solution 3

Hi,
You can use connection string like below,

C#

SQLCONNSTR = "Data Source= 192.168.1.1 ;Initial Catalog=DAName;User ID=Test & ";pwd=Test;"

And make sure SQL server has configure to received the connection over TCPIP

Posted 26-Aug-16 2:00am

Subodh_Bhatkar

Comments

Frankie-10589807 6-Sep-16 9:27am

Thank you so much Subodh. Will get back to you.

Frank

Frankie-10589807 · Answer 3 · 2016-08-23T23:59:00

Thank you so much Mr. FranzBe, though i am yet to try this solution, but i keep getting the feeling that i have to use the ip address of the server to be able to establish connection over the internet with the app. However, even in the same domain, the connection is failing with the IP address, unless i use the server name and stated above. I try to seek the help of a DBA again to see how this will go. What solution that works, i will post on this platform.

Thanks again, Mr. FranzBe

Remote SQL database connection

3 solutions

Solution 1

Solution 3

Solution 2

Add your solution here

Preview 0