将 MS SQL SERVER 2008 R2 EXPRESS 与 C# Windows 应用程序一起使用？

发布于 2024-12-04 05:29:26 字数 1265 浏览 0 评论 0原文

我创建了使用 MS SQL 数据库的 ac# windows 应用程序（2.0 框架）。
在开发过程中，我使用了 MS VISUAL STUDIO 2010 和 SQL 2008 MANAGEMENT STUDIO。
我在开发过程中的连接字符串是：

SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=SL;Integrated Security=True");

一切正常......

现在我想在客户端系统上运行这个应用程序。
这样就在客户端系统上成功安装了 MS SQL SERVER 2008 EXPRESS。
停止我系统的sql服务，并从我的机器复制.mdf和.ldf文件并粘贴到客户端的“c:\Database\”中。但跑步的时候问题就来了。

发生网络相关或特定于实例的错误建立与 SQL Server 的连接。找不到服务器或无法访问。验证实例名称是否正确 SQL Server 配置为允许远程连接。（提供者：SQL 网络接口，错误：26 - 定位服务器/实例时出错指定）

我多次尝试过的连接字符串是：

SqlConnection con = new SqlConnection("Data Source=.\\MSSQLEXPRESS;Initial Catalog=SL;Integrated Security=True");

SqlConnection con = new SqlConnection("Data Source=.\\MSSQLEXPRESS;Initial Catalog=SL;Persist Security Info=True;User ID=sa;Password=pass");

SqlConnection con = new SqlConnection("Data Source=.\\MSSQLEXPRESS; AttachDbFilename =C:\\Database\\SL.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True");

当我在连接字符串中使用 User ID=sa;Password=pass 时，我得到：

“sa”身份验证失败

我是否遗漏了一些步骤或做错了？请告诉我从系统复制数据库后应该做什么。 C# 中的连接字符串应该是什么？

谢谢！

原文

I've created a c# windows application(2.0 framework) which uses MS SQL database.
During developing I've used MS VISUAL STUDIO 2010 and SQL 2008 MANAGEMENT STUDIO.
My connection string during development is :

SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=SL;Integrated Security=True");

Everything works fine....

Now I want to run this application on client system.
So installed MS SQL SERVER 2008 EXPRESS successfully on client system.
Stopped sql services of my system and copied the .mdf and .ldf files from my machine and pasted in "c:\Database\" of the client.
But while running comes the problem.

A network-related or instance-specific error occurred while
establishing a connection to SQL Server. The server was not found or
was not accessible. Verify that the instance name is correct and that
SQL Server is configured to allow remote connections. (provider: SQL
Network Interfaces, error: 26 - Error Locating Server/Instance
Specified)

The connection strings which I've tried many times are :

SqlConnection con = new SqlConnection("Data Source=.\\MSSQLEXPRESS;Initial Catalog=SL;Integrated Security=True");

SqlConnection con = new SqlConnection("Data Source=.\\MSSQLEXPRESS;Initial Catalog=SL;Persist Security Info=True;User ID=sa;Password=pass");

SqlConnection con = new SqlConnection("Data Source=.\\MSSQLEXPRESS; AttachDbFilename =C:\\Database\\SL.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True");

When I use User ID=sa;Password=pass in connection string I get:

authentication failed for 'sa'

Am I missing some steps or doing wrong? Please tell me what should I do after copying the database from my system. What should be my connection string in C#?

Thanks !

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

蓝海 2024-12-11 05:29:26

您只需将附加数据库所需的文件复制到 SQLExpress 中，尝试使用 OSQL，或者将客户端工具安装到具有 SQLExpress 的 PC 上并附加数据库。

回复收藏 0 原文

暖风昔人 2024-12-11 05:29:26

首先，请务必查看 Sres的回答。
如果您没有在连接字符串中告诉 SQL Server 附加数据库（使用 AttachDbFilename，如第三个示例中所示），则必须按照他所说的自己进行附加。

关于您的三个连接字符串示例：它们都仅在某些情况下有效。您可能需要查看 connectionstrings.com。

以下是您的三个连接字符串，以及对其问题的简短说明：

Data Source=.\MSSQLEXPRESS;Initial Catalog=SL;Integrated Security=True

-->这使用您的应用程序正在运行的当前 Windows 用户。所以当前的windows用户必须对客户端机器上的数据库有权限。

数据源=.\MSSQLEXPRESS;初始目录=SL;持久安全信息=True;用户ID=sa;密码=pass

-->这使用特殊的“sa”用户帐户。为了使其工作，您必须确保满足以下先决条件：

必须设置混合模式身份验证（如果不这样做，则只有 Windows 身份验证，而 'sa' 是 SQL Server认证用户名）
需要指定sa的密码，当然必须与开发机上的相同。

但这无论如何都不是最好的解决方案。 “sa”是具有完全权限的管理员帐户，您不应使用管理员帐户通过您的应用访问 SQL Server。
如果您确实想使用 SQL Server 身份验证（而不是 Windows 身份验证），最好创建一个具有应用程序所需的最低必要权限的新帐户。

数据源=.\MSSQLEXPRESS; AttachDbFilename =C:\Database\SL.mdf;集成安全性=True;连接超时=30;用户实例=True

-->与第一个连接字符串相同的问题（当前 Windows 用户必须具有权限）。
另外，需要在 SQL Server 中启用 User Instance=True。
引用自 connectionstrings.com：

要使用用户实例功能，您需要在
SQL 服务器。这是通过执行以下命令来完成的：
sp_configure '用户实例已启用'，'1'。要禁用
功能执行 sp_configure '用户实例已启用'，'0'。

First of all, be sure to check out Sres' answer.
If you don't tell SQL Server in the connection string to attach your database (with AttachDbFilename, like in your third example), you have to do the attaching yourself as he said.

Concerning your three connection string examples: all of them only work under certain circumstances. You might want to check out connectionstrings.com.

Here are your three connection strings, plus short explanations of their issues:

Data Source=.\MSSQLEXPRESS;Initial Catalog=SL;Integrated Security=True

--> This uses the current Windows user that you app is running under. So the current windows user must have permissions on the database on the client's machine.

Data Source=.\MSSQLEXPRESS;Initial Catalog=SL;Persist Security Info=True;User ID=sa;Password=pass

--> This uses the special 'sa' user account. In order for this to work, you have to make sure that the following prerequisites are met:

Mixed mode authentication must be set up (if you don't do this, you only have Windows authentification, and 'sa' is a SQL Server authentification user name)
the password of 'sa' needs to be specified, and of course it must be the same as on the development machine.

But this is not the best solution anyway. 'sa' is an admin account with full permissions, and you shouldn't use an admin account to access SQL Server with your app.
If you really want to use SQL Server authentification (instead of Windows authentification), it's better to create a new account with the minimal necessary permissions that your app needs.

Data Source=.\MSSQLEXPRESS; AttachDbFilename =C:\Database\SL.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True

--> same issue as with the first connection string (the current Windows user must have permissions).
Plus, User Instance=True needs to be enabled in SQL Server.
Quote from connectionstrings.com:

To use the User Instance functionality you need to enable it on the
SQL Server. This is done by executing the following command:
sp_configure 'user instances enabled', '1'. To disable the
functionality execute sp_configure 'user instances enabled', '0'.

回复收藏 0 原文