Access denied when connecting to SQL Server 2005

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Access denied when connecting to SQL Server 2005

Author

Topic

surfoman
Starting Member

4 Posts

Posted - 2009-10-01 : 19:42:09

Hello,
I have a SQL Server 2005 Standard Edition database on a Windows 2003 IIS6 server.

I have taken a copy of another database from another server and restored it within this server. The previous SQL server database was an Express edition.

When I try to run my ASP test connection script, the page hangs rather than display a confirmation statement.

I ran a trace of all the server events using Filemon and the following line kept appearing:

22:36:03 PM w3wp.exe:6100 OPEN C:\Program Files\Microsoft SQL Server\90\Shared\instapi.dll ACCESS DENIED MyComputerName\IUSR_theiusrname

Although I have only copied databases between SQL server Express editions, I have never experienced this issue before. I have never had to apply permissions to any folders to get the connection to work.

The database has a user which has full owner permission and connects using SQL Server autentication. This is how I have always done it in the past. I have attached the ASP connection script so you can see what I am trying to do.

Many thanks

-----------------------------------------------------------------
Const const_SQLDatabaseName = "testdatabase"
Const const_SQLuid = "sqlusername"
Const const_SQLPassword = "sqlpassword"

response.write "<p>Start connection</p>"
Set oConn = Server.CreateObject( "ADODB.Connection" ) '
with oConn
.Provider = "SQLNCLI"
.ConnectionTimeout = 1000
.CommandTimeout = 1000
.ConnectionString = "server=.\SQLEXPRESS;database=" & const_SQLDatabaseName & ";uid=" & const_SQLuid & ";password=" & const_SQLPassword
.Open
end with

response.write "<p>End connection</p>"

oConn.Close
set oConn=nothing
-----------------------------------------------------------------

If I leave the script running for a while then the following output occurs.

Start connection
Microsoft SQL Native Client error '80004005'
SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].
/dbconnect.asp, line 22

surfoman
Starting Member

4 Posts

Posted - 2009-10-02 : 13:52:51

Ok I managed to find the solution in the end.

The server name in the connection string had the wrong SQL server name. We carried over the server name from the previous SQL Server Express server. All works fine now.

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2009-10-04 : 17:19:18

In classic ASP its best to put your connection string in the global.asa file or simply in a SSI ( server side include ) and then reference it.

ie centralized location

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources