远程SQL数据库连接 [英] Remote SQL database connection

问题描述

大家好日子。我试图通过Windows窗体应用程序连接到远程sqlserver数据库。但唯一的选择我不断得到连接字符串是以下我希望知道它有多安全：



数据源= 190.190.168.100,1433;网络库= DBMSSOCN;初始目录= myDataBase;用户ID = myUsername;密码= myPassword;



代替用户名，我使用点燃的sa帐户我怀疑字符串的安全性。



远程服务器在Windows Server 2012上运行，db是sql2014。





如有任何帮助，我们将不胜感激。



提前付款

弗兰克。



我尝试过：



我使用以下连接在同一个域内运行的字符串运行良好，但在互联网上与其他网络域失败。



数据源= NEPTUNSERVER \ NTSERVER;初始目录= ITEM_MGR ; integrated security = True

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"

推荐答案

当您的SQL Server以混合模式运行并且您想要从其他域登录时，您应该继续使用SQL Server身份验证模式方法。



你使用sa犹豫不决。在SQL Server中为应用程序创建用户（如果不允许，请从数据库管理员处获取帮助）。

授予此用户不超过应用程序所需的权限。

您可以使用脚本生成所需的sql命令

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

 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

你可以使用如下的连接字符串，

Hi,
You can use connection string like below,

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

并确保SQL服务器已配置为通过TCPIP接收连接

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

非常感谢FranzBe先生，尽管我还没有尝试这个解决方案，但我一直觉得我必须使用服务器的IP地址才能通过互联网与应用程序建立连接。但是，即使在同一个域中，连接也会因IP地址而失败，除非我使用服务器名称并在上面说明。我试着再次寻求DBA的帮助，看看它会如何发展。什么解决方案有效，我将在这个平台上发布。



再次感谢FranzBe先生

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

这篇关于远程SQL数据库连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！