Connection Open Exemptions

Hi,
Using VB .NET is there a way to return/track the reason why a connection has
failed to open. I want to be able to track the following reasons for
failure:
1. User doesn't have the correct permissions set to open the database
2. Unable to open the connection because the user setting are wrong, (server
name, username, password)
3. Using the wrong authentication - NT authentication instead of SQL Server
authentication and vis versa
Many thanks
Hello 11Oppidan,
Is this a duplicated post of "Security and Logins with ADO.NET"? If so,
please check my answer there and if you need any further assistance on this
particular issue, please reply to me in that thread so I can follow up with
you in time. Also, please don't post the same question in newsgroups in the
future so that our engineers can work on your question efficiently. Your
understanding and cooperation is appreciated.
If you'd like to troubleshoot the problem from the client computer, I
recommend you perform the following steps:
1. Install SQL client tools on the client computer. Check if the user can
log into SQL server using SQL Query Analyzer (QA). Check if the user can
run the SQL statement successfully.
If the user can log into SQL server using QA, it indicates that the user
has permissions on this SQL server.
Note: The user account can be either Windows account or SQL account.
After log into SQL server in QA, if the user can run the SQL statement
successfully, it indicates that the user has the correct permissions on the
database.
If the user cannot log into SQL server using QA, please try to log into QA
as sa or an administrative user. If the issue still exists, you can refer
to the following article:
How to troubleshoot connectivity issues in SQL Server 2000 (827422)
http://support.microsoft.com/default...B;EN-US;827422
2. Contact Product Support Services (PSS) to get mdactrace tool
If you want more detailed information about ADO connection, I recommend
that you open a Support incident with Microsoft Product Support Services
(PSS) so that a dedicated Support Professional can work with you in a more
timely and efficient manner. If you need any help in this regard, please
let me know.
For a complete list of Microsoft Product Support Services phone numbers,
please go to the following address on the World Wide Web:
<http://support.microsoft.com/directory/overview.asp>
If you are outside the US please see http://support.microsoft.com for
regional support phone numbers.
3. The output file of "sqldiag Utility".
Please execute the following command at the DOS prompt:
"C:\Program Files\Microsoft SQL Server\MSSQL\Binn\sqldiag.exe"
SQLdiag.exe is a command line utility that puts all the SQL server error
logs and other configuration settings into a text file.
Note:
a. Above command use the default folder. You may need to replace the right
folder which sqldiag.exe locates.
b. The generated log file SQLdiag.txt is located in Log folder.
Check the output file.
4. Windows application log
The following information is for your reference:
Viewing the Windows Application Log
<http://msdn.microsoft.com/library/de...-us/adminsql/a
d_perfmon_5qnn.asp>
Check the Windows Application Log file.
You may send me all output files for research at v-sguo@.Microsoft.com
I hope above information is helpful.
Sophie Guo
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/tec...rview/40010469
Others: https://partner.microsoft.com/US/tec...pportoverview/
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/default...national.aspx.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hi Sophie,
Thank you again for your time and respnse to my previous question regarding
the security settings. We solved that user problem thanks to your previous
answer. The problem was the user did not have read access to the master db
and we were querying this to return a list of db names attached to the
server. The dba gave her read permission to the master db and that resolved
the problem.
As a consequence of that problem I have realised that I need to change the
code I use to return a list of dbs names for the user to select, and that
was why I asked this second question.
Maybe this is not the best way to do it - but currently I query the master
db to return a list of attached db names. I then go through these opening
each db to check a certain tbl exists. If it does I know is one of the dbs
the user protentially needs to query and I add the name to a combobox item.
This seems to work fine if the user has the correct permissions and user
settings.
Unfortunately though dbas are not too generous with permissions, and
different companies operate very different security policies! So I want to
be able to establish the reason why a connection fails to open when called
in code (VB .NET), and use a try & catch to trap the reason why this is
happening so I can report it back to the user so they can take the right
action to resolve it. The three reasons I can think of why a connection
might fail to open were.
1. User doesn't have the correct permissions set to access the database
2. Unable to open the connection because the user has incorrectly entered
their settings (server
name, username, password)
3. The user has selected to use the wrong authentication - NT authentication
instead of SQL Server
authentication and vis versa.
So far I can only find how to return the state of the connection e.g.
cnn.State <>ConnectionState.Open then Exit Function
But it would be good if I could also trap an SqlException which indicated
which of these different reasons caused the open call to fail!
Your advice and feedback is much appreciated.
"Sophie Guo [MSFT]" <v-sguo@.online.microsoft.com> wrote in message
news:v8sanEoCFHA.2504@.cpmsftngxa10.phx.gbl...
> Hello 11Oppidan,
> Is this a duplicated post of "Security and Logins with ADO.NET"? If so,
> please check my answer there and if you need any further assistance on
> this
> particular issue, please reply to me in that thread so I can follow up
> with
> you in time. Also, please don't post the same question in newsgroups in
> the
> future so that our engineers can work on your question efficiently. Your
> understanding and cooperation is appreciated.
> If you'd like to troubleshoot the problem from the client computer, I
> recommend you perform the following steps:
> 1. Install SQL client tools on the client computer. Check if the user can
> log into SQL server using SQL Query Analyzer (QA). Check if the user can
> run the SQL statement successfully.
> If the user can log into SQL server using QA, it indicates that the user
> has permissions on this SQL server.
> Note: The user account can be either Windows account or SQL account.
> After log into SQL server in QA, if the user can run the SQL statement
> successfully, it indicates that the user has the correct permissions on
> the
> database.
> If the user cannot log into SQL server using QA, please try to log into QA
> as sa or an administrative user. If the issue still exists, you can refer
> to the following article:
> How to troubleshoot connectivity issues in SQL Server 2000 (827422)
> http://support.microsoft.com/default...B;EN-US;827422
>
> 2. Contact Product Support Services (PSS) to get mdactrace tool
> If you want more detailed information about ADO connection, I recommend
> that you open a Support incident with Microsoft Product Support Services
> (PSS) so that a dedicated Support Professional can work with you in a more
> timely and efficient manner. If you need any help in this regard, please
> let me know.
> For a complete list of Microsoft Product Support Services phone numbers,
> please go to the following address on the World Wide Web:
> <http://support.microsoft.com/directory/overview.asp>
> If you are outside the US please see http://support.microsoft.com for
> regional support phone numbers.
>
> 3. The output file of "sqldiag Utility".
> Please execute the following command at the DOS prompt:
> "C:\Program Files\Microsoft SQL Server\MSSQL\Binn\sqldiag.exe"
> SQLdiag.exe is a command line utility that puts all the SQL server error
> logs and other configuration settings into a text file.
> Note:
> a. Above command use the default folder. You may need to replace the right
> folder which sqldiag.exe locates.
> b. The generated log file SQLdiag.txt is located in Log folder.
>
> Check the output file.
>
> 4. Windows application log
> The following information is for your reference:
> Viewing the Windows Application Log
> <http://msdn.microsoft.com/library/de...-us/adminsql/a
> d_perfmon_5qnn.asp>
> Check the Windows Application Log file.
> You may send me all output files for research at v-sguo@.Microsoft.com
> I hope above information is helpful.
>
> Sophie Guo
> Microsoft Online Partner Support
>
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
> Business-Critical Phone Support (BCPS) provides you with technical phone
> support at no charge during critical LAN outages or "business down"
> situations. This benefit is available 24 hours a day, 7 days a week to all
> Microsoft technology partners in the United States and Canada.
>
> This and other support options are available here:
> BCPS:
> https://partner.microsoft.com/US/tec...rview/40010469
> Others: https://partner.microsoft.com/US/tec...pportoverview/
>
> If you are outside the United States, please visit our International
> Support page:
> http://support.microsoft.com/default...national.aspx.
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
>
>
|||Hi 11Oppidan,
As for getting the error info when fail to establish a sqlconnection, I
think the Exception throwed by the runtime
has containded some useful infos. For example:
When we create a new SqlConnection with invalid connectionstring such as
SqlConnection conn = new SqlConnection("#$#@.4234#@.$#@.$");
We'll Receive a "System.ArgumentException" indicate that we've provided
incorrect paratmeters
If the connstring is valid on syntax, then when we call Connection.Open,
the runtime will also throw SqlException with the proper exception message
according to the actual error. For example:
if we use a incorrect login user(username and password), it will return a
SqlException and its
"Message" property will contain
"Login failed for user 'XXXX'."
Also, if we input the wrong server name, the error Message may be something
like:
"SqlServer dosn't exist or access denied."
So generally, the normal way is to add try...catch block around our sql
code and read the Exception.Message property to get the error general error
info.
BTW, as for the 3rd reason you mentioned:
===================
3. The user has selected to use the wrong authentication - NT
authentication
instead of SQL Server
authentication and vis versa.
============================
I'm afraid the runtime (also sqlserver) may not support such checking. The
database will only valdating the comming request according to the request's
authentication type, but won't check whether it is using the wrong
authentication type or whether it may succeed through another type.
HTH. Thanks.
Steven Cheng
Microsoft Online Support
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)