在Excel中的“连接详细信息”中隐藏SQL Server用户名和密码 [英] Hide SQL server user name and pwd in Connection details in Excel

问题描述

大家好，

我使用excel Sheet以数据透视表的形式显示数据。由于数据将非常庞大，我将直接连接到SQL Server以从存储过程中获取数据。一切正常，但我想隐藏用户的用户名和密码。

你能建议我怎么做。



这些是我到目前为止尝试过的以下事项。

1.保护表格。这有助于我禁用Data-> Properties选项，但Data-> Connections仍处于打开状态。

2.试图隐藏/禁用Ribbon / whole Data部分中的按钮 - 不起作用因为它不可能这样做。

3.使用VBA删除连接。这可以正常工作，因为Data-> Connections和Data->属性都被禁用，但我的用户将双击数据透视表以查看基础数据。因此，在删除连接后，Excel不允许查看数据并说此数据透视表的连接已被删除



我真的很担心我的问题并且不要不希望用户获取我的SQL服务器的用户名/ PWD。



谢谢，

Sajid

Hi All,
I am using an excel Sheet to show data in form of pivot tables. Since data is going to be huge i am connecting to SQL Server directly to get data from a stored proc. Everything is working fine however i want to hide the user name and password from users.
can you suggest me how to do this.

these are the following things i have tried so far.
1. Protect the sheet. This helps me in disabling the Data->Properties option but Data->Connections is still open.
2. Tried to hide/disable the buttons in Ribbon/ whole Data section - Didn't work as its not possible to do so.
3. Delete the connections using VBA. this works fine as Data->Connections and Data->Properties both are disabled but then my users are going to double click on the pivot table to see the underlying data. So after deleting connections, Excel is not allowing to see data and Saying "The connection for this pivot table has been deleted"

I am really worried about my issue and don't want users to get the Username/PWD for my SQL server.

Thanks,
Sajid

推荐答案

请先阅读我的评论。



据我所知，没有办法隐藏连接选项（密码和用户名）。您可以编写宏来在MS Excel中创建数据透视表，然后删除连接：

在Excel 2010中使用VBA创建数据透视表和图表 [ ^ ]

Macro从新的Pivot缓存创建数据透视表 [ ^ ]

请保护VBA代码 [ ^ ]在开始向用户分发工作簿之前。



如果你想用VB.NET这样做，这里是一个例子：以编程方式创建数据透视表 [ ^ ]

Please, read my comment first.

As far as i know there is no method to "hide" options of connection (password and user name). You can write macro to do create pivot table in MS Excel and then to remove connection:
Creating PivotTable Reports and Charts with VBA in Excel 2010[^]
Macro to Create a Pivot Table from New Pivot Cache[^]
Please, protect VBA code[^] before you start distributing workbook to users.

If you would like to do it using VB.NET, here is an example: Creating a PivotTable Programmatically[^]

这篇关于在Excel中的“连接详细信息”中隐藏SQL Server用户名和密码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！