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.

about connections and Login

Hey guys,

I have created dtsx packages and i have one master package which would run couple of child packages.

1. I have all on my local machine.I want others who r working with me access these.So what do i need to do?for them to access...

2.what would be the best way in executing these packages.

the way it is set now is i had 3 connections set for each package.

a.Source connection

b.Destination Connection.

c.One more connection(which is used with in the process of execution)

Can i set connections in common for all the packages in one location.

3.i also want to set some logging settings to my master package too.and i want this package to run everyday automatically at a particular time how can this be done.

How can i do that.

please let me know.

sureshv wrote:

1. I have all on my local machine.I want others who r working with me access these.So what do i need to do?for them to access...

What you mean, to edit them? just use VSS or place them in a shared location. To execute them, place them in a shared location and then set up a Job (SQl Agent) to run them.

sureshv wrote:

2.what would be the best way in executing these packages.

Via SQL Agent job would be my preferred method

sureshv wrote:

Can i set connections in common for all the packages in one location.

No, but you can use package configuration instead.

sureshv wrote:

3.i also want to set some logging settings to my master package too.and i want this package to run everyday automatically at a particular time how can this be done.

To schedule them, again, use a SQL Agent Job. For logging just enable package logging.

About Connections and component mappings

1.After creating pacakages what i am concerned about is that when i move then to different locations do they work just the same as the had been.

2.About the mapping with in the components like lookups,and the variable with in conditional split or etc....do they get disturbed with any change in the location of the textfiles or anything of this kind of location change.

3.Is is adviceable transforming 5 to 6 text file with in one package in parallel

Please let me know

Thanks

1. They should work the same way. Just make sure the account executing the package has required permissions on all enviroments; but that is nothing to do with the package itself.

2. Same as above

3. That may be a matter of taste. I rather preffer to encapsulate an smaller set of functionality. A packag per every flat file may be; they could be run in parallel anyway.

|||Also, for #1, you can use package configurations to allow for easier maintenance of the file paths.|||

How can i use package configurations?For easier maintenance.How dod i do it.

Please let me know.

|||

Package configuration allows you to virtually overwrite any object property within a SSIS package. It comes very handy to deploy packages to different environments as you can override connection strings, paths; variable values; etc.

Here are a copule of links that talks about it:

http://msdn2.microsoft.com/en-us/library/ms141682.aspx

http://www.sqlis.com/?26

|||Keep in mind that a package is just an XML file that can always be opened in "notepad". Any concerns that you have could be resolved using ctrl F.