MS SQL-Server:Windows 7上无法ODBC-连接吗? [英] MS SQL-Server: cannot ODBC- connect on Windows 7?

问题描述

我在Windows 7上为SQL-Server创建ODBC数据源连接时遇到问题.

I have a problem creating an ODBC data source connection for SQL-Server on Windows 7.

问题摘要:只能为"master"数据库创建ODBC数据源连接，而不能为其他任意数据库创建.

Problem summary: ODBC datasource connection can be created for the "master" database only, not for an additional, arbitrary database.

我在网上搜索过Google，但没有找到解决方案.详细信息如下:

I have googled around the Net and haven't found a solution. Here are the details:

1. 我在Windows 7 64位系统上安装了MS SQL Server.详细信息:SQLEXPRESS版本，版本:10.1.2531.0，内部版本:2007.100.2531.0，实例ID:MSSQL10.SQLEXPRESS.

使用"SQL Server配置管理器"，它表示:

Using "SQL Server Configuration Manager", it says that:

SQL Server(SQLEXPRESS) running

我还设置了:登录方式:内置帐户->本地系统".

I have also set up: "Log on as: built-in account -> Local System".

1. 使用"Microsoft SQL Server Management Studio"(版本10.0.1600.22)我已连接到数据库，用户名和密码为空.然后，我创建了一个名为"Test1"的新数据库，并将创建的文件定向到位于:"F:\ Data \ SQL Server \ Test1.sdf". 我继续创建几个(简单的)表，填充了几行，并成功地运行了几个查询-全部使用"Microsoft SQL Server Management Studio".到目前为止一切顺利.

1. Using "Microsoft SQL Server Management Studio" (version 10.0.1600.22) I have connected to the database, with blank username and password. I then have created a new database, called "Test1", and directed the created file, to reside at: "F:\Data\SQL Server\Test1.sdf". I continued with creating a couple of (trivial) tables, populated a couple of lines, and successfully ran a couple of queries - all using "Microsoft SQL Server Management Studio". So far so good.

但是，我需要使用ODBC连接从外部程序连接数据库. 因此，我尝试使用"ODBC数据源管理器"(\％sysdir％\ SysWOW64 \ odbcad32.exe版本6.1.7600.16385)(64位)，创建一个数据源接口.这是我的经验:(我将dsn称为"SYS_DB"):

But, I need to interface to the database from an external program, using an ODBC connection. So, using "ODBC Data Source Administrator" (\%sysdir%\SysWOW64\odbcad32.exe version 6.1.7600.16385) (64 bit), I am trying to create a data source interface. Here are my experiences: (I call the dsn: "SYS_DB"):

3a.当进入系统DNS"->添加"->"SQL Native Client 10.0"时； (对于驾驶员)；服务器:PLUTO \ SQLEXPRESS; 具有集成的Windows身份验证"； (所有其他选项:默认)，当我按:测试数据源"时，它显示:

3a. When going "System DNS" ->Add-> "SQL Native Client 10.0"; (for the driver); Server: PLUTO\SQLEXPRESS; "With integrated Windows authentication"; (all other options: default), when I press: "Test Data Source", it says:

... ... Address PLUTO\SQLEXPRESS' was used to establish the connection.
... TESTS COMPLETED SUCCESSFULLY!

(PLUTO是计算机名称).

(PLUTO is the computer name).

3b.问题是，我需要连接到"Test1"数据库，而不是默认的"master"数据库.

3b. The problem is, I need to connect to the "Test1" database, not to the default "master" database.

因此，在"ODBC数据源管理器"窗口的第三个窗口中，当我执行以下操作:->将默认数据库更改为:Test1，我得到一个窗口，显示:

So, in the third window in the "ODBC Data Source Administrator" window, when I go: ->Change the default database to: Test1, I get a window saying:

The database entered is not valid

如果我返回一个窗口，并且取消选中:连接到SQL Server以获取其他配置选项的设置"，然后检查:附加数据库文件名":F:\ Data \ SQL Server \ Test1.sdf(如我在上面的2中提到的)，数据源测试失败，并显示以下信息:

If I go one window back, and uncheck: "Connect to SQL Server to obtain setting for the additional configuration options", and then I check: "Attach Database Filename": F:\Data\SQL Server\Test1.sdf (like I mentioned in 2. above), the datasource test fails with:

Attempting connection
[Microsoft][SQL Server Native Client 10.0][SQL Server] 
The header for file 'F:\Data\SQL Server\Test1.sdf' is not a valid database file header.
The FILE SIZE property is incorrect.

3c.尝试设置驱动程序时:系统DNS"->添加"->"SQL Server"； (而不是上面3a中的"SQL Native Client 10.0")，其行为是相同的:连接到默认的"master"数据库时成功，而尝试连接到"Test1"数据库时失败，并带有与以下相同的错误消息在3b中.以上.

3c. When trying to set the driver: "System DNS" ->Add-> "SQL Server"; (instead of "SQL Native Client 10.0" as in 3a. above), the behavior is identical: success when connecting to the default "master" database, and failure when trying to connect to "Test1" database, with the same error message as in 3b. above.

1. 注意:当我访问"SQLcmd -L"时，它表示:

1. Note: when I go: "SQLcmd -L", it says:

Servers:
PLUTO
PLUTO\SQLEXPRESS

我已经在整个Google上进行了搜索，但没有找到解决方案.您的帮助将不胜感激.

I've googled throughout the Net and haven't found a solution. Your help will be appreciated.

许多TIA-海伦

更多信息(3-16):当我进入时:

Further information (3-16): when I go:

>sqlcmd -S pluto\sqlexpress -Q "select getdate()"
2013-03-16 10:07:04.380 (1 rows affected)

与默认(主")数据库的连接工作正常.

so connection with the default ("master") database works fine.

但是当我走时:

>sqlcmd -S pluto\sqlexpress -d Test1 -Q "select getdate()"
Msg 4060, Level 11, State 1, Server PLUTO\SQLEXPRESS, Line 1
Cannot open database "Test1" requested by the login. The login failed.
Msg 18456, Level 14, State 1, Server PLUTO\SQLEXPRESS, Line 1
Login failed for user 'PLUTO\MyUser'.

所以看来问题出在该服务器上新数据库的用户特权/访问权限上，即"Test1".

So it seems that the problem is with user privileges/access rights to the new database on that server, namely: "Test1".

任何人都可以帮助检查/分配对SQL Server上特定数据库的权限吗?

Can anyone help with how to inspect/assign permissions to a specific database on SQL Server?

许多TIA

海伦

推荐答案

事实证明，"Test1"数据库位于紧凑"实例中. (在我的OP中看到"sdf"数据库文件扩展名可能会引起注意).

It turns out that the "Test1" database was in the "compact" instance. (That could have been noticed seeing the "sdf" database file extension in my OP).

所以这就是ODBC连接看不到它的原因. (SQL Server Compact Edition没有ODBC管道.)

So this was the reason the ODBC connection did not see it. (SQL Server Compact edition does not have an ODBC pipe).

这篇关于MS SQL-Server:Windows 7上无法ODBC-连接吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！