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
 .NET Inside SQL Server (2005)
 Connection String

Author  Topic 

knockyo
Yak Posting Veteran

83 Posts

Posted - 2008-02-23 : 01:45:01
Currently im using this kinds of connection string at web.config

<add name="SQLconnstr" connectionString="Data Source=Server1;Initial Catalog=DB1;User ID=sa;Password=password;" providerName="System.Data.SqlClient"/>

But since the security policy as mention below:
The domain administrator (Company) can provide you the domain account (in the format Company\service_XXXX_XXXX) by the request.

From your side you should achieve for the application to work in the windows authentication mode only (associated either with domain or with DB server) by means of using the integration security mode for the application (when NO password is taken to specify in the configuration file). As I said, instead of domain account (in the format Company\service_XXXX_XXXX) you could use the database server machine account (in the format DB_SERVERNAME\service_XXXX_XXXX). This would allow you to test the application in the windows authentication mode straight away when NO the domain account (in the format Company\service_XXXX_XXXX) is arranged for us.

few question:
(1) Actually what it means above?
(2) will affect my current connection string?
(3) how to change the connection string to fullfill the requirements above?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-02-23 : 14:11:34
Use trusted connection: http://www.connectionstrings.com/?carrier=sqlserver

When using Windows Authentication, you do not provide the login or password. It uses the current security context, meaning the person who is logged into the client machine.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

knockyo
Yak Posting Veteran

83 Posts

Posted - 2008-02-24 : 07:41:55
I already try using the connection string below:

Data Source=Server1;Initial Catalog=DB1;Integrated Security=SSPI;

it seem like not work at my local PC.

Few Question:

(1) Is that I need to configure the SQL Server?
(2) If i did not put any pass & username, how can the program recognize the connection?

Sorry, im not familiar using this kinds of connection, hope can have a clear guide.
Go to Top of Page

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-02-24 : 10:35:48
Try this..

1.Create a new text document
2.save it and close
3.rename it with extension .udl
4.open the file

you will now see a GUI that will help you connect to the database. Once you can connect successfuly, rename the file back to .txt and you will have a notepad version of the proper connection string.

The .udl extension is a little known bonus called from Microsoft Data Link...comes in very handy..may help you out in this case.



Poor planning on your part does not constitute an emergency on my part.

Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-02-24 : 12:57:27
quote:
Originally posted by knockyo

I already try using the connection string below:

Data Source=Server1;Initial Catalog=DB1;Integrated Security=SSPI;

it seem like not work at my local PC.

Few Question:

(1) Is that I need to configure the SQL Server?
(2) If i did not put any pass & username, how can the program recognize the connection?

Sorry, im not familiar using this kinds of connection, hope can have a clear guide.



1. Whatever Windows accounts that will be used to connect will need to be added to the database server.

2. As mentioned in my last post, it uses the current security context instead of a SQL userid and password.

Always post your errors instead of just saying it doesn't work. We can't read minds.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

knockyo
Yak Posting Veteran

83 Posts

Posted - 2008-02-24 : 13:01:18
thanks ur way.

I already try it, and i choose the "Windows Login" rather than username & password.

But my connection still failed.

Is that i need to configure the SQL Server security or add owner?

Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-02-24 : 13:05:18
But you have to add the account to the database server first.

Where is the error that you are getting??? Remember we can't read minds nor can we see your computer.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

knockyo
Yak Posting Veteran

83 Posts

Posted - 2008-02-24 : 21:22:53
you means i need to create a User at security?

There is no error message, it just cannot establish the connection only at my program.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-02-24 : 22:34:13
Yes you need to add the user to security.

You should program your application so that it produces errors, especially during the debug phase.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

knockyo
Yak Posting Veteran

83 Posts

Posted - 2008-02-26 : 06:36:43
Since i already put the TRY CATCH function but it won't get any fail message.

It only show the error message that i define "Access Denied"

here is my coding:


Try
If UserID <> "" And Pwd <> "" Then
If objpmOT.Login(UserID, Pwd) Then
usr = objpmOT.UserById(UserID)
If usr Is Nothing Then
' === Invalid login information ===
lblError.Text = "* Invalid user name!"
lblError.Visible = True
SetFocus(txtUsername)

Else
' *** Get access string ***
accstr = objpmOT.GetAccessStringsByUserGroup("")

If usr.Groups.Count > 0 Then
usr.CheckAccesses(accstr)
Dim iCount As Integer
For iCount = 1 To accstr.Count
Session(accstr.Value(iCount)) = CStr(accstr.Key(iCount)).ToUpper
Next
End If
Session("Username") = UserID
Response.Redirect("Frame/Index.aspx", False)

End If
Else
' === Invalid login information ===
lblError.Text = "* Access denied!"
lblError.Visible = True
SetFocus(txtUsername)

End If
Else
' === Incomplete login information ===
lblError.Text = "* Invalid login info!"
lblError.Visible = True
SetFocus(txtUsername)

End If
Catch ex As Exception
lblError.Text = "* " & ex.Message
lblError.Visible = True
Finally
If Not accstr Is Nothing Then accstr = Nothing
If Not usr Is Nothing Then
usr.Dispose()
usr = Nothing
End If
If Not objpmOT Is Nothing Then
objpmOT.Dispose()
objpmOT = Nothing
End If
End Try


I already try many type of connection string for the trusted connection, but it still failed.

Any idea?
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-02-26 : 13:37:27
Did you add the Windows account that you are logged on with to the SQL Server?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
   

- Advertisement -