Sql server connection string problem

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

, +

Hello everyone ,

Here is my problem ,

I develop a form application in my computer. And here is my connection string :

"Server=localhost;Database=atak_db;Trusted_Connection=True;"

and this is works.

Now i want to install this form application at another computer.

Other computer has sql server also and same database has been attached already in this computer.

But my connection string isn't work in this computer. I have tried a lot of things and connectionstrings also.

Do you have any suggestions about this issue ? What can be my problem ? How can i solve this ?

Posted 1-Nov-15 23:58pm

kozmikadam

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Andy Lanng · Accepted Answer · 2015-11-02T00:11:00

Solution 2

If you have copies the database from your machine to another you may need to change the security.

Bizarrely, if the target server has the same login credentials as the newly attached database, they do not sync. You have to remove the previous credentials and add them back from the server creds.

As you're using Trusted_Connection=True, check the windows account is set up on the db and that it has access writes (or is owner or whatever) of the attached database.

This is an annoyance I have to go through with EVERY db deployment I do >_<

Hope that helps ^_^
Andy

UPDATE:
Step by step:

Open MSSQL Management Studio
Find the database and expand
Expand Security / Users
Delete the windows user account
Under the server:
Expand Security / Logins
If the users windows account is there appear:

Right Click and select Properties

If the account is not there:

Right click the Logins folder
Select New Login
Add the windows user account

Go to User Mapping on the left
check the database to add them to
select the roles they should have
Click OK

br mode="hold" />If all goes to plan then the windows user should be added to the database and the Trusted_Connection=True; should now work

Posted 2-Nov-15 0:11am

Andy Lanng

Updated 2-Nov-15 0:54am

v2

Comments

kozmikadam 2-Nov-15 6:33am

Hi Andy ,

I haven't so much experiance with sql server management studio. can you explain step by step ?

I didn't get "remove the previous credentials". Can you explain more ?

There is 2 option in security. One of in my db. And another one in under Databases. And there are 4 users , which one is the windows account ?

Can it be , can't connect to new computers sql server ?

Andy Lanng 2-Nov-15 6:55am

Please check the updated solution ^_^

kozmikadam 2-Nov-15 6:59am

Thanks for that ! But i have one more question. Which one is the windows user account ? dbo - guest - INFO SHEMA -sys and i can delete just guest. So can i assume it's the windows account user ?

Andy Lanng 2-Nov-15 7:02am

It's best to use the named account: so Domain\username

I'm pretty sure it won't exist on either the server or the db

kozmikadam 2-Nov-15 7:07am

Ok ,i found it under the server > sequrity > logins and i add to my db owner.

But still i can't connect.

Should i make any change in my connection string ?

Here is the error description :

Bu iletişim kutusu yerine JIT hata ayıklamayı çağırma
hakkında detaylı bilgi için bu iletinin sonuna bakın.

************** Özel Durum Metni **************
System.Data.SqlClient.SqlException (0x80131904): Sunucuyla bağlantı kurulurken ağ ile ilgili veya örneğe özel bir hata oluştu. Sunucu bulunamadı veya sunucuya erişilemiyor. Örnek adının doğru olduğunu ve SQL Server'ın uzak bağlantılara izin verdiğini doğrulayın. (provider: Named Pipes Provider, error: 40 - SQL Server için bağlantı açılamadı) ---> System.ComponentModel.Win32Exception (0x80004005): Sistem belirtilen dosyayı bulamıyor
konum: System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
konum: System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
konum: System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, Boolean withFailover, SqlAuthenticationMethod authType)
konum: System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover)
konum: System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout)
konum: System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance)
konum: System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken)
konum: System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
konum: System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
konum: System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
konum: System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
konum: System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
konum: System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
konum: System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
konum: System.Data.ProviderBase.DbC

Andy Lanng 2-Nov-15 7:53am

For the English speakers:
System.Data.SqlClient.SqlException (0x80131904): the connection to the server or instance-specific error occurred on establishing a network. The server was not found or the server is unreachable. Verify that the instance name is correct and that SQL Server to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) ---> System.ComponentModel.Win32Exception (0x80004005): The system can not find the file specified
Location: system.data.sqlclient.sqlınternalconnection.onerr is (SqlException exception, Boolean breakconnectio's, Action`1 wrapcloseınactio's)

Andy Lanng 2-Nov-15 7:55am

This error message tells you that the app cannot find the server. As you are using localhost this means that either the instance is not running (which would mean that you could not see it in SQl management Studio) or that the instance name is not root.

When you open MSSQL Management Studio, what does it say in the "Server name"?

kozmikadam 2-Nov-15 7:57am

server name = .\SQLEXPRESS but i also try this , also i tried computers name. still same error.

I tried this connection string and still same error :

"Data Source=.;Database=atak_db;Trusted_Connection=True;"

Andy Lanng 2-Nov-15 8:03am

Ah ha - This is a named instance.
"." is the same as "localhost"
"SQLExpress" is the service
"." will actually be "localhost\defaultInstance"

What you need is this:
"Data Source=.\SQLExpress;Database=atak_db;Trusted_Connection=True;"

kozmikadam 2-Nov-15 8:05am

i tried this also. But let me try again :)

Edit : Tried and we got the same error. I don't get it. What is the problem.

Andy Lanng 2-Nov-15 8:41am

The error says it cannot find the host :?

Try Data Source=localhost\SQLEXPRESS;Initial Catalog=atak_db;Integrated Security=SSPI;

kozmikadam 2-Nov-15 8:51am

Yes error says it cannot find the host.

Tried and i can say , it isn't work. Also isn't work on my computer.

Andy Lanng 2-Nov-15 9:00am

Your computer may not be sqlexpress

hmm - it's very odd.

try these steps (may instance is not set up correctly):
You need to verify that the SQL Server service is running. You can do this by going to Start > Control Panel > Administrative Tools >
Services, and checking that the service SQL Server (SQLEXPRESS) is running. If not, start it.
While you're in the services applet, also make sure that the service SQL Browser is started. If not, start it.
You need to make sure that SQL Server is allowed to use TCP/IP or named pipes. You can turn these on by opening the SQL Server Configuration Manager
Start > Programs > Microsoft SQL Server
2012 > Configuration Tools, and make sure that TCP/IP and Named Pipes are enabled.
otherwise
run Start->Run->Services.msc If so, is it running?
if it not running then
It sounds like you didn't get everything installed. Launch the install file and chose the option "New installation or add features to an existing installation". From there you should be able to make sure the database engine service gets installed.

kozmikadam 2-Nov-15 9:12am

I already enabled tcp/ip and named pipes. sql server is running also. sql browser is not running. But i enable it. Now it's running also.

And now , i will try reinstall sql server.

I have sql server management studio in this computer also.

Andy Lanng 2-Nov-15 9:27am

yeah, reinstall may help :S

kozmikadam 2-Nov-15 9:27am

I reinstall sql server. And now we have some progress !

Our new error is : Cannot open database "atak_db" requested by the login the login failed. Login failed for user "Msi\atak"

Andy Lanng 2-Nov-15 9:28am

ah ha - that it good news

Andy Lanng 2-Nov-15 9:28am

hmm - odd user. Is that the user login?

kozmikadam 2-Nov-15 9:32am

yes it's the user login.
i tried a few connection string with trusted_connection or user sa . error is the same. login failed for user "msi\atak"

Andy Lanng 2-Nov-15 9:35am

hmm - find the user under database / security / users. Go to properties and check under membership that they have some access rights (i.e. db_owner)

kozmikadam 2-Nov-15 9:37am

i selected all of them already. :/

Andy Lanng 2-Nov-15 9:38am

When you added the user, did you use the "Search" button. That would confirm you have the correct user.

If so, post your current connection string

kozmikadam 2-Nov-15 9:40am

yes it's the correct user.

SERVER=.//SQLEXPRESS;Database=atak_db;Trusted_Connection=true;

Andy Lanng 2-Nov-15 9:47am

you can also use "SERVER=.\SQLEXPRESS;Database=atak_db;Trusted_Connection=true;"

Ok - lets test something:

Go through the steps to create a new user, but create an sql user. Uncheck "enforce password policy"

Set up the mapping to the database with just db_owner

Then use the following connection string:
"SERVER=.\SQLEXPRESS;Database=atak_db;User Id=[username];Password=[password];"

Let me know if that works

kozmikadam 2-Nov-15 9:57am

i tried , but again it says : Login failed for user "msi/atak"
why it's tring to login with this user ? I don't get it.

Andy Lanng 2-Nov-15 9:59am

are you sure you're changing it in the correct config?

kozmikadam 2-Nov-15 10:04am

i create a new user with sql server authentication.
also set a password for it.
uncheck enforce password policy
in user mapping select db_owner ( public is selected default , i can't uncheck it )

Andy Lanng 2-Nov-15 10:21am

That's fine. Sounds like you set up the user ok. I'm hoping it has a generic name and not a name like 'msi/atak'.

Where are you setting the connection string? are you sure this is taking effect?

kozmikadam 2-Nov-15 10:27am

yes it's a uniq user.

my gridview is filling with form_load event.

Andy Lanng 2-Nov-15 10:53am

But where are you setting the connection string?

Oh - in the code? Ok - make sure that you are rebuilding it correctly and copying the correct version

kozmikadam 2-Nov-15 10:59am

yes in code. i have 2 connection strings ( they are same. that my mistake and now i use 2 connection strings. ) one of them in form load event and the other one is in settings.designer.cs ( this one comes from gridviews)

Andy Lanng 2-Nov-15 11:30am

Well I don't know what to tell you. If you use the connection string I gave you then you should not be getting that error message :S

kozmikadam 2-Nov-15 15:31pm

Hi Andy ,
Thank you for your help.I solve the problem ! I restart the sql server and now it works !

Thank you for your interest about this issue !I learned a lot of think today thanks to you !

Andy Lanng 3-Nov-15 5:27am

Ha ha - restart *face palms*

Glad you got it working ^_^