如何在Windows *上使用带有TinyTDS *和*集成安全连接的activerecord-sqlserver-adapter，而无需在纯文本中保存密码

I'm trying to use Rails 3.1. with the activerecord-sqlserver-adapter (3.1.1) and tiny_tds (0.4.5) on a Windows machine. In reading about TinyTDS and it's use of FreeTDS it looks like I can use Integrated Security (aka Windows Integrated security/NTLM) by putting a domain-qualified name as the user name (e.g. DOMAIN\userbob). But the docs still want me to type my domain user's password in the database.yml file. That's bad practice because it's insecure and doesn't take advantage of single-sign on, which is part of the point for Integrated Security.

我正在尝试使用Rails 3.1。在Windows机器上使用activerecord-sqlserver-adapter（3.1.1）和tiny_tds（0.4.5）。在阅读有关TinyTDS及其使用FreeTDS时，看起来我可以通过将域限定名称作为用户名（例如DOMAIN \ userbob）来使用集成安全性（也称为Windows集成安全性/ NTLM）。但是文档仍然要我在database.yml文件中键入我的域用户密码。这是不好的做法，因为它不安全并且没有利用单点登录，这是集成安全性的一部分。

Can I connect without saving a password in plain text in a file? e.g.

我可以连接而不保存文件中的纯文本密码吗？例如

developement:
    adapter: sqlserver
    mode: dblib
    dataserver: localhost
    database: dev_db
    username: DOMAIN\userbob
#    password: no_no_please_dont_make_me_type_it_here

But, even if I put a password I get the following error:

但是，即使我输入密码，我也会收到以下错误：

TinyTds::Error: Unable to connect: Adaptive Server is unavailable or does not exist
    from C:/Ruby192/lib/ruby/gems/1.9.1/gems/tiny_tds-0.4.5-x86-mingw32/lib/tiny_tds/client.rb:60:in `connect'
    from C:/Ruby192/lib/ruby/gems/1.9.1/gems/tiny_tds-0.4.5-x86-mingw32/lib/tiny_tds/client.rb:60:in `initialize'

I know my server is running and the current user context can connect because this works:

我知道我的服务器正在运行，当前用户上下文可以连接，因为这有效：

sqlcmd -S localhost -d dev_db -E

Any insights? Is it possible? If not, it should be.

任何见解？可能吗？如果没有，它应该是。

4 个解决方案

#1

Enable TCP/IP in "SQL Server Network Configuration" in SQL Server Configuration Manager utility. Then restart SQL Server service.

在SQL Server配置管理器实用程序中的“SQL Server网络配置”中启用TCP / IP。然后重新启动SQL Server服务。

Regarding security you will need to supply your own credentials otherwise it will use sa account.

关于安全性，您需要提供自己的凭据，否则将使用sa帐户。

#2

Apparently SQL Server 2008 is setup by default to ONLY allow Windows Authentication. To change that you have to open the Management Studio, Right click on your server and select Properties. Select Security and click the "SQL Server and Windows Authentication mode" under Server authentication. This will at least allow you to connect directly to the server until TinyTDS makes the needed changes to allow Windows Authentication.

显然，SQL Server 2008默认设置为仅允许Windows身份验证。要更改它，您必须打开Management Studio，右键单击您的服务器并选择“属性”。选择安全性，然后单击服务器身份验证下的“SQL Server和Windows身份验证模式”。这至少允许您直接连接到服务器，直到TinyTDS进行必要的更改以允许Windows身份验证。

#3

I think the code making the connection is trying to make the process impersonate the security context supplied by the credentials instead of passing the default credentials through to SQL server.

我认为进行连接的代码正在尝试使进程模拟凭据提供的安全上下文，而不是将默认凭据传递给SQL Server。

This would allow you to specify different credentials to the current security context. I've used this trick to connect to a server requiring SSIS from an untrusted domain.

这将允许您为当前安全上下文指定不同的凭据。我已经使用这个技巧连接到需要来自不受信任域的SSIS的服务器。

I don't know TinyTDS/FreeTDS, perhaps null or empty credentials would make it use the default security context. Try:

我不知道TinyTDS / FreeTDS，也许null或空凭据会使它使用默认的安全上下文。尝试：

developement:
    adapter: sqlserver
    mode: dblib
    dataserver: localhost
    database: dev_db

要么

developement:
    adapter: sqlserver
    mode: dblib
    dataserver: localhost
    database: dev_db
    username:
    password:

#4

Try to use

尝试使用

developement:
    adapter: sqlserver
    mode: dblib
    dataserver: localhost\SQLEXPRESS
    database: dev_db
    username: DOMAIN\userbob

or else do you set 2 variant authentification in your's sql server? then try to connect with sa user...

或者你在你的sql server中设置了2个变体验证？然后尝试与sa用户连接...

#1