devxlogo

SQL Web Authentication

SQL Web Authentication

Question:
Given that NTCR is only good in a network environment, how can I redirect a user back to a login page (or trap something) if I pass their username and password into a OLE DB connection string and use SQL authentication? When a user is not in the SQL user list the error is: Microsoft OLE DB Provider for SQL Server (0x80040E4D). Login failed for user ‘weblogin’. Apart from installing additional software on the server is there a elegant way to handle this and take the user to a nicer place?

Answer:
One simple technique is to set “On Error Resume next” prior to making the database hit. Then check the value of the Err object when you return. If it is not zero, you know an error took place and can proceed to a nicer rejection page.

On Error Resume NextobjConn.Open "dsn", "userid", "password"If Err  0 Then   display neat error messageend if

Encapsulate the above in its own function so that the On Error statement does not affect your other code.

Obviously, this is a simple solution for your immediate problem. Perhaps you can rethink your design so that user names and passwords of users to your site are stored in a table against which you validate using a common SQL Server login name and password rather than have to generate and maintain SQL Server users.

devxblackblue

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist