SQL Server 2012 classic asp connection string

Question

I have a SQL Server 2012 Express installation with a new DB called BRD that I have created. I have also created a test table (tempDemo), and a test stored procedure (getStList) in the BRD database. The stored procedure works when I run it in the query window so I believe the table and stored procedure are legit. The SQL Server is set to "SQL Server and Windows Authentication mode".

I then attempted to create a Classic ASP page that then connected to the SQL Server using the following connection string:

objConn.ConnectionString="Provider=SQLOLEDB;Server=XPSI7\SQLEXPRESS;Database=BRD;Integrated Security=SSPI;"

This fails with the following message:

"Microsoft OLE DB Provider for SQL Server error '80004005' Cannot open database "BRD" requested by the login. The login failed."

When I change the database to MASTER instead of BRD the ASP page does not error out. I'm just testing the connection string by opening it and then closing it, but it appears to work.

I've looked at the security settings for MASTER and BRD in the Object Explorer, but have failed to notice a difference. I've also looked at the IIS_IUSRS for the folders, but no difference either - not sure if this is necessary anyway.

Answer 1

SQL Server authenticates your login at the server level. Then it tries to open the database you've asked to connect to. At this point, you need to either map a database-level user to the server-level login, or use a server-level login that inherently has sufficient privileges to use the database.

A simple (but not secure) way to demonstrate this using SQL Server Authentication:

USE master;
GO
CREATE LOGIN foo
  WITH PASSWORD = 'bar', CHECK_POLICY = OFF;
GO
USE BRD;
GO
CREATE USER foo FROM LOGIN foo;
GO
EXEC sp_addrolemember N'db_owner', N'foo';
GO

Now in your ASP connection string, use:

objConn.ConnectionString = "Provider=SQLNCLI;Data Source=XPSI7\SQLEXPRESS;Initial Catalog=BRD;User ID=foo;Password=bar;"

You can also map whatever login you're using to a database user simply doing:

USE BRD;
GO
CREATE USER [YourDomain\IIS_IUSRwhatever] 
  FROM LOGIN [YourDomain\IIS_IUSRwhatever];
GO

That will grant them access to the database, but it will be up to you to decide what permissions to grant. This assumes that you are allowing anonymous access to the web server; if IIS is challenging and accepting Windows authentication, you'll need to do the above for the user(s) that will be submitting their credentials. In either case you should be able to continue using the connection string you have now.

Answer 2

Try the following connection string:

strConnection = "Provider=SQLNCLI10;Server=server_name;Database=" & BD_REMOTE_INITIAL_CATALOG & ";Uid=" & BD_REMOTE_USER_ID & "; Pwd=*****;"