c# Winform 连接 sql server 2005 的问题

hphubei

SqlConnection (.NET)


Standard Security:

"Data Source=Aron1;Initial Catalog=pubs;User Id=myUsername;Password=myPassword;"
   - or -
"Server=Aron1;Database=pubs;User ID=myUsername;Password=myPassword;Trusted_Connection=False"
   (both connection strings produces the same result)




Trusted Connection:

"Data Source=Aron1;Initial Catalog=pubs;Integrated Security=SSPI;"
   - or -
"Server=Aron1;Database=pubs;Trusted_Connection=True;"
   (both connection strings produces the same result)

(use serverName\instanceName as Data Source to use an specifik SQLServer instance)
Connect via an IP address:
"Data Source=190.190.200.100,1433;Network Library=DBMSSOCN;Initial Catalog=pubs;User ID=myUsername;Password=myPassword;"
(DBMSSOCN=TCP/IP instead of Named Pipes, at the end of the Data Source is the port to use (1433 is the default))
Enabling MARS (multiple active result sets):

"Server=Aron1;Database=pubs;Trusted_Connection=True;MultipleActiveResultSets=true"
Note! Use ADO.NET 2.0 for MARS functionality. MARS is not supported in ADO.NET 1.0 nor ADO.NET 1.1

Streamline your Data Connections by Moving to MARS, by Laurence Moroney, DevX.com >>
Attach a database file on connect to a local SQL Server Express instance:

"Server=.\SQLExpress;AttachDbFilename=c:\asd\qwe\mydbfile.mdf;Database=dbname;Trusted_Connection=Yes;"
   - or -
"Server=.\SQLExpress;AttachDbFilename=|DataDirectory|mydbfile.mdf;Database=dbname;Trusted_Connection=Yes;"
   (use |DataDirectory| when your database file resides in the data directory)
Why is the "Database" parameter needed? Answer: If the database was previously attached, SQL Server does not reattach it (it uses the attached database as the default for the connection).
Using "User Instance" on a local SQL Server Express instance:

"Data Source=.\SQLExpress;integrated security=true;attachdbfilename=|DataDirectory|\mydb.mdf;user instance=true;"
The "User Instance" functionality creates a new SQL Server instance on the fly during connect. This works only on a local SQL Server 2005 instance and only when connecting using windows authentication over local named pipes. The purpose is to be able to create a full rights SQL Server instance to a user with limited administrative rights on the computer. To enable the functionality: sp_configure 'user instances enabled','1' (0 to disable)
Using SQL Server 2005 Express? Don't miss the server name syntax: SERVERNAME\SQLEXPRESS (Substitute "SERVERNAME" with the name of the computer)

hphubei

问题到底是在哪儿那？

hphubei

访问另外一个台式机上面的 sql server 2000 就没有任何问题，郁闷啊

hphubei

刚刚试过了，从台式机访问本子上面的 sql server 2000 就没有问题，用同一个程序访问 sql server express 2005 就是不行

hphubei

http://www.z6688.com/info/47673-1.htm

这里面也是一样的错误，为什么别人就能够轻易搞定，我怎么改都不行那？

hphubei

错误提示："An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) "

装完 sqlserver2005(Express版），为了便于管理，你还需要去下一个管理器（SQLServer2005_SSMSEE.msi），微软的官方网站有。

1、开启sql2005远程连接功能：
配置工具->SQL Server 外围应用配置器->服务和连接的外围应用配置器->打开MSSQLSERVER节点下的Database Engine 节点,先择"远程连接",接下建议选择"同时使用TCP/IP和named pipes",确定后,重启数据库服务.

2、登陆改为混合模式:
打开manage管理器->以windows方式连接并进入数据库->右键点击你的数据服务器->属性->security>选中Sql server and windows Authentication

3、新建Sql server方式的用户名和密码:
maage管理器->security->右键点击logins->new login...->选中sql server authentication->设置login name 和password（confirm password）
最好去掉“enforce password expiration”前的小钩，否则每次登陆都要修改密码。
/*
manage管理器->windows Authentication>new query>sp_password null,'sa123456','sa'
这样就设置了一个用户名为sa ,密码为:sa123456的用户,下次在登陆时,可以用Sql server方式,
用户名为sa ,密码为:sa123456的用户进数据库了.
*/

4、做完上面三步后,这样写连接字符串就可以顺利进入数据库了(server=.\\sqlexpress;uid=用户名;pwd=密码;database=master";

搞了半天原来我一直写的server=(local),晕倒。

这也是别人的一个错误，跟我的一样的，但是我这边这么改就是不行

hphubei

在装sql server express 的机器上面运行 sqlcmd -s wcs-mark\sqlexpress出现提示：

mo_yuan_ming

不要使用动态端口，而那时指定1433

mo_yuan_ming

在SQlServer configuration manager中网络配置需要配置见图

配置好后，在SqlServer安全认证中将仅Windows认证改为混合认证，添加何时的数据库登陆，即可。

hphubei

最初由 mo_yuan_ming 发布
[B]不要使用动态端口，而那时指定1433 [/B]

嗯，是使用的这个端口，自己指定的