Showing posts with label servers. Show all posts
Showing posts with label servers. Show all posts

Thursday, March 29, 2012

Access Denied - Remote connection to SSIS

We have 2 SQL2005 servers currently. 1 is a dev box, the other production. To grant remote access to SSIS, I added my developers to the local Distributed COM Users group and also gave them remote Access and remote Activation on the MsDtsServer component.

This worked just fine. Recently we applied SP1 and the follow-on hotfixes to all SQL components. I followed the install order prescribed in the Q article and applied all component fixes. My developers are now unable to access SSIS remotely unless I add them to the local administrator's group. I don't see anywhere that a log gets generated to help me track down what's going on. Nothing shows up in my Windows security log, despite enabling audit logging on: Accout Logon Events, Logon Event, Object Access, and Privilege Use.

Is this a know issue in SP1 or the hotfixes? I'm hesitant to apply the patches to my production box until I can resolve this issue.

Thanks
Steve

With the lack of responses I'm assuming I'm out of luck. Anyone have suggestions on how to troubleshoot this? Is there some reg entry I can flip to enable debugging or even use of the event log for SSIS?

Thanks

|||When you say you have added developers to DCOM Users Groups, is this what you have done - Connecting to a Remote Integration Services Server (17th July 2006) - http://msdn2.microsoft.com/en-us/library/aa337083.aspx ?|||

That article only mentions the need to give them access to the msdtsserver. There is another article that also mentions that they must be placed in the local Windows group called: "Distributed COM Users". I have followed both articles. As I mentioned, this configuration was working until a short time after applying the SP1 and Hotfix patches for SQL2005. It is still working on our production box that has not yet been patched.

Thanks

Steve

|||

I am getting the same behaviour, we have also got SP1 installed.

|||

After applying SP1 and the Hotfixes, we never rebooted Windows (never received a notification that we should). This weekend we applied our monthly round of OS patches. Now that the server has been rebooted access is working correctly again.

I guess the lesson here is that, if you've followed all of the instructions and they still don't work, do a full reboot - restarting services doesn't appear to be enough.

You may consider this thread closed.

Thanks

Steve

Access Denied

The issue i have is:

3 Servers: DC1, SQL1 and Web1

All have IIS and an replicated intranet site http:/server/intranet
All have 2 DSN connections set exactly the same except one using TCP one using named pipes)

DC1's web app will access a search on SQL1 using named pipes but not TCP
SQL1 as above
WEB1's web app will not access a search on SQL1 using any of the DSN connections.

Errors recieved

Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC SQL Server Driver][Named Pipes]Access denied.

SQL is in mixed secuirty mode.

I am unable to work out the above no matter how much I play with teh permissions.

IIS on all Servers use Windows Authentication.
The SQL database is set to allow all Domain Users access.

Please Help

Did you allow WEB1\IUSR_WEB1 in your SQL Server?|||

I have tried this but when trying to add WEB1\IUSR_WEB1 it says "Error 15401: WIndows NT user or group 'WEB1\IUSR_WEB1' not found. Check the name again.

I would assume this is due to WEB1\IUSR_WEB1 being an local account on WEB1.

I know this al seems basic but this is the first time I have had this issue.

Would this be that I need to add WEB1\IUSR_WEB1 to a group account in the domain and then allow this account access.

I have also tried a DSNLESS connection with the SQL system account but still get access denied.

|||You cannot add local users of other computer to principals in SQL Server as SQL Server cannot trust the identity of the user crendetials. I assume that you are currently using anonymous authentication with the mentioned user above. If you want to use the actual connecting users to gain access to the database with their individual rights on the database, you will have to turn of anynomous authentication and enable Windows Integrated authentication on the Web server. (If the web server and the SQL Server are on different machines, you will additionallY have to setup delegation) If you just want to have the Web Server connect with its Service account to SQL Server you will have to let it run under a domain account which is trusted by SQL Server. All users accessing the database will then use this credentials to auithenitcate at the database. If you don′t want both of the solutions and don′t want to use WIndows authentication per se, you will have to change your query string to let the application on the Web Server connect with a common SQL Server user which is the used by every web application consuming user.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Thanks for the advice.

I have managed to get the database to connect using Jens K. Suessmeyer advice.

What has worked is:

DSN Connection
SQL_Database with TCP Connection
USER: sql_system_account, PASSWORD: xxxxxxxx

ASP page Connection string.
<%
'if request("submit") = "true" then
Dim objConn
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.ConnectionString = "dsn=SQL_Database; uid=sql_system_account; pwd=xxxxxxxx"
objConn.Open
'end if
%>

Note: Both must be in place otherwise errors still occour.

I will still have to look into the actaul web connection users and delegation in sql for the databases.

I would assume the problem was due to the Web server being originally setup on the Domain Controller and the sql databases being moved to a new server (Server 2003) and differant pernissions being lost.
Active directory was on the same server as the web server so authentication was handled differantly once the intranet and sql were moved to application servers.

Sunday, March 25, 2012

access data in remote SQL server with trusted authority

I have two win2003 servers. One is IIS and the another is SQL server. I
can use MyConnection=new
SqlConnection("server=SQLServerName;database=myDBName;UID=sa;PWD= mypwd")
to access my data. But I can not use MyConnection=new
SqlConnection("server=SQLServerName;database=myDBName;Integrated
Security=SSPI") to access it. If I put both IIS and SQL Server in one
machine, I can do anything. How can I do something to allow trusted
authority work ?

ThanksYou might find these KB articles (or one of the ones they link to)
useful:

http://support.microsoft.com/defaul...kb;en-us;316989
http://support.microsoft.com/defaul...kb;en-us;247931

If these don't help, I suggest you clarify what you mean by "can not
use" - do you get a specific error message?

Simonsql

Access cannot see MSDE when network cable disonnected

When my users are in the field and try to switch databases the
connection tool cannot see any servers even thought I know they are
there. The data link properties dialog does not see them. Also my data
entry program also cannot see them but is using sqldmo to list the
servers. However my SQL Service manager (inside my task bar) Can see
them. Any pointers on how to let the access project or the sqldmo
objects see the servers when they are disconnected. I even tried using
ODBC with no luck.
Thanks for any help.
Shane
Hello,
Personnaly, I use the following
Dim ListSvr As NameList
Set ListSvr = SQLDMO.ListAvailableSQLServers
' I add the servers to a collection after removing the ones I don't want
to show
' After that,
Set oServer = New SQLDMO.SQLServer2
Set ListSvr = oServer.ListInstalledInstances
' This function always work but only for the local instances (named or not)
' I add the needed servers to the other collection but I don't put the
servers already listed before
Marc Allard
Allcomp
Shane Lim wrote:
> When my users are in the field and try to switch databases the
> connection tool cannot see any servers even thought I know they are
> there. The data link properties dialog does not see them. Also my data
> entry program also cannot see them but is using sqldmo to list the
> servers. However my SQL Service manager (inside my task bar) Can see
> them. Any pointers on how to let the access project or the sqldmo
> objects see the servers when they are disconnected. I even tried using
> ODBC with no luck.
> Thanks for any help.
> Shane
>
|||That is PERFECT!!
On Fri, 25 Feb 2005 10:39:39 +0100, Allcomp <marc@.nospam.allcomp.be>
wrote:
[vbcol=seagreen]
>Hello,
>Personnaly, I use the following
>Dim ListSvr As NameList
>Set ListSvr = SQLDMO.ListAvailableSQLServers
>' I add the servers to a collection after removing the ones I don't want
>to show
>' After that,
>Set oServer = New SQLDMO.SQLServer2
>Set ListSvr = oServer.ListInstalledInstances
>' This function always work but only for the local instances (named or not)
>' I add the needed servers to the other collection but I don't put the
>servers already listed before
>Marc Allard
>Allcomp
>
>Shane Lim wrote:

Tuesday, March 20, 2012

Access 2003 Project Connecting to SQL 2000 & 2005 Servers

We have an Access 2003 Project that it's primary server is a SQL 2000 server. When I link a table to the 2000 server and execute the view, I get data with no problem. I link a table to the 2005 server and execute the view...once again, no problem. However, if I try to write a query to pull data from both views......times out. I have changed the timeout value to "0" and never returns the data.

So then I created a new Access 2003 Project to use the 2005 as the primary server. Once again I create a linked table and execute the view on the SQL 2000 server and it works just fine. Then create a linked table to the 2005 server and the view works just fine. However, here's the difference. When I try to create a query to pull data from both views, I get:

  1. This version of Microsoft Office Access doesn’t support design changes with the version of Microsoft SQL Server your Access project is connected to. See the Microsoft Office Update Web site for the latest information and downloads. Your design changes will not be saved.

  2. You have connected to a version of SQL Server later than SQL Server 2000. The version of Visual Studio or Access that you are using was released before the version of SQL Server to which you are connected. For this reason, you might encounter problems. Please check with Microsoft to see if there is a service pack that you should apply to Visual Studio or Office in order to get support for the version of SQL Server to which you are connected. You can continue but any new object types might not be enumerated, and it will not be possible to save any objects or database diagrams that you create using the Visual Database Tools.

Any assistance would be GREATLY appreciated. I'm stuck and wish I could make this happen.

PS:....

The version of Access 2003 specifically is: Microsoft ? Office Access 2003 (11.6566.6568) SP2

I've even made two linked tables to the 2005 server and executed each view by themselves and it worked fine. However, if I try to create a query off of those linked tables, same errors just posted above.

I'm afraid that Access ADPs aren't going to work all that smoothly with SQL Server 2005--they are created for SQL Server 2000 and earlier. If you're creating a new app, it's recommended that you use an mdb with linked tables because it gives you more flexibility. ADPs were designed around the scenario that you would connect to *one* SQL Server database, not multiple databases. One option if you must use ADPs is to create views to access data in another database. You can then use those views in your Access FE.

--Mary

sql

Sunday, March 11, 2012

acces multiple servers

Hi,

I need to access a table from another server in my procedure...I am now connected to say SERVERA...I need to access another table SERVERB.TABLE2 in the same procedure...

Is there a way to do that...

I need to connect to two servers from the same procedure to get data..

Please get back

Thanks,

Hi Swapna,

Yes, there are a few methods, however the most straightforward would be to create a linked server.

Have a look at BOL or http://www.databasejournal.com/features/mssql/article.php/3085211 for more info.

Cheers,

Rob

|||Thanks a lot Rob...It was very helpful

Thursday, March 8, 2012

Abt DTS mass migration

Two servers with different name and IP
We have to transfer around 500 DTS packages to the new server. But can not restore the MSDB to new server, and also can not DTS all the packages to new server in one step. Any tips for DTS mass migration? Or we have to save as one by one manually? Thanks in advanced.Hi there,

I don't know if this helps you further, but I use the "DTSBackup 2000" tool for transferring DTS packages from one server to another. You can find out more about this tool at http://www.sqldts.com/?242

Greetings,
Carsten|||Thank u very much. Let me try...

Originally posted by CarstenK
Hi there,

I don't know if this helps you further, but I use the "DTSBackup 2000" tool for transferring DTS packages from one server to another. You can find out more about this tool at http://www.sqldts.com/?242

Greetings,
Carsten