How to Login to SQL server in ASP pages?
S N
I want to login to the SQL server through the ASP pages and access the
database for displaying the results on ASP pages.
I tried creating the connection string using the UDL editor with
1.) Use Windows NT Integrated Security:
The connection string as designed by the UDL editor is as below:
[oledb]
; Everything after this line is an OLE DB initstring
Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security
Info=False;Initial Catalog=adminSQLServerDatabase;Data
Source=127.0.0.1\SQLEXPRESS
The Test Connection from within the UDL editor gives message of success for
the above connection string.
However, when we copy the above connection string inside an ASP page, the
connection fails giving an error message.
2. ) When we use the option of "USE a specific user name and password"
inside the UDL editor, then we get error message- "Test connection failed
because of an error in initialising provider. Login failed for user 'sa'."
The error message continues even if we use the default internet guest user
account for the UDL editor.
Further, if we use the connection string string of this scenario, the
connection fails in the ASP page.
Please advise on how to connect to the database from inside the ASP pages
using suitable connection string. Also advise if OLEDB is the way to go?
S N
Erland Sommarskog
> I am running SQLServerExpress on my desktop PC having IIS on Windows XP
> SP3. I want to login to the SQL server through the ASP pages and access
> the database for displaying the results on ASP pages. I tried creating
> the connection string using the UDL editor with 1.) Use Windows NT
> Integrated Security: The connection string as designed by the UDL editor
> is as below:
>
> [oledb]
> ; Everything after this line is an OLE DB initstring
> Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security
> Info=False;Initial Catalog=adminSQLServerDatabase;Data
> Source=127.0.0.1\SQLEXPRESS
>
> The Test Connection from within the UDL editor gives message of success
> for the above connection string. However, when we copy the above
> connection string inside an ASP page, the connection fails giving an
> error message.
I must ask: is this a quiz where I get more points for a correct
answer the less information I need? Or is there any other particular
reason you did not disclose the error message?
In any case, you have a connection string that wants to log into
SQL Server with Windows authentication. This means that you log into
SQL Server with the same user as you are logged into Windows.
When you test the connection, you are logged in as yourself, and your
Windows login apparently maps to a login in SQL Server, either by
your login name as such, or a Windows group you are a member of.
But IIS runs as a service, and is logged in someone else. You could
configure IIS to log in as you, but that would be a very bad idea. I'm
not an IIS person, so I don't know what IIS runs as, but I believe it
is one of the system accounts LocalSystem or LocalService.
While you could add them as a login in SQL Server, I don't really
think this is recommendable. And it may not work at all if IIS and
SQL Server are on different machines. I believe the recommendation
for web applications is to use an SQL Server login.
> 2. ) When we use the option of "USE a specific user name and password"
> inside the UDL editor, then we get error message- "Test connection failed
> because of an error in initialising provider. Login failed for user 'sa'."
When you this option, you must of course specify a valid username
and password. (And that login should not be sa, but a login that
only have the permissions needed to run the application.)
Furthermore, SQL Server authentication have to be enabled. The default
when you install SQL Server, is that SQL Server authentication is
disabled. You can enable SQL Server authentication from SQL Server
Management Studio. Right-click the server, and select the Security
tab. You need to restart SQL Server fot the setting to take effect.
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx