January 8, 2009 at 3:31 am
Hi,
I have an application that is based on a SQL 2005 Express database.
Previously that application was running using MSDE and the access to the database was always made using the "sa" user ID and "sa" password.
Now we switched to SQL 2005 Express and I'm facing problems accessing the database using the "sa" account.
SQL 2005 Express installation is ran from a batch file which passes the following parameters through the command line:
SQLEXPRSP2_ITA.EXE -q /norebootchk /qb reboot=ReallySuppress addlocal=all instancename=CGORGDB SQLBROWSERAUTOSTART=1 SQLSECURITYMODE=SQL SAPWD=cg220708cgInfo DISABLENETWORKPROTOCOLS=2 SQLACCOUNT="NT AUTHORITY\SYSTEM" SQLBROWSERACCOUNT="NT AUTHORITY\SYSTEM" AGTACCOUNT="NT AUTHORITY\SYSTEM" ASACCOUNT="NT AUTHORITY\SYSTEM" RSACCOUNT="NT AUTHORITY\SYSTEM"
After the installation is completed, the program (Visual Basic 6.0 program) creates the database and the ODBC DSN to connect the database.
The connection string to create the database is the following:
"Provider=SQLOLEDB.1;" & _
"Persist Security Info=True; " & _
"Data Source=.\CGORGDB;" & _
"Trusted_Connection=true;" & _
"User ID=" & UID & "; Password=" & UPWD & ";" & _
"Initial Catalog=Master;"
where UID and UPWD are "sa" and "cg220708cgInfo"
The database and the ODBC DSN are created without errors.
When the program connects to the database to create all the tables, uses this connection string:
"Provider=SQLNCLI;" _
& "Server=TP077\CGORGDB;" _
& "Data Source=TP077\CGORGDB;" _
& "Database=CGORGDB;" _
& "DataTypeCompatibility=80;" _
& "MARS Connection=True;" _
& "User ID=sa; Password=cg220708cgInfo;" _
& "Trusted_Connection=false;"
but I get the error (translated from italian) "user "sa" is not associated with a SQL Server trusted connection".
The type of authentication that I would like to use is the "Mixed Mode (Window+SQL Server) but probably the connection string that I'm supplying is not correct.
If I try to connect to the database using "SQL Server Management Studio Express" choosing the SQL authentication, after I typed "sa" and "cg220708cgInfo" the following error appears:
A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - no process on the other side of the pipe)(Microsoft SQL Server, error: 233)
Where is the mistake?
Thank you, regards.
Roberto G.
January 8, 2009 at 4:56 am
I've made a further investigation and I noticed that right after the end of the installation, (logging into "SQL Server Management Studio Express" with "Windows Authentication") checking properties of the instance "CGORGDB", even though during the installation of SQL 2005 Express I declared "SQLSECURITYMODE=SQL", the authentication is still "Windows only" and the "sa" account is then disabled.
Changing the authentication mode manually to "Windows and SQL Server" and enabling the "sa" account, I can login succesfully.
Then the problem seems to be during the installation, for some reason it is not getting all the command line parameters.
Regards,
Roberto
January 8, 2009 at 8:53 pm
Sorry... bum post. See the next one.
January 8, 2009 at 9:35 pm
Then the problem seems to be during the installation, for some reason it is not getting all the command line parameters.
Do you mean the database creation?
January 9, 2009 at 1:04 am
No, I mean SQL Server 2005 Expr. installation.
January 9, 2009 at 6:51 am
"Data Source=.\CGORGDB;"
"Data Source=TP077\CGORGDB;"
Are these two lines correct in your cStrings?
In Management Studio on the login window, what is the name of the server?
The default instance is SERVERNAME\SQLEXPRESS. In your cString you have this: Server=TP077\CGORGDB. Did you change the default at install. Verify the instance name in your cString matches the one you see in Management Studio.
If you can log in from Management Studio as sa using SQL Authentication, you should be able to connect from remote.
Look in SQL Server Surface Area Configuration and make sure you have "Local and Remote" connections enabled. Are the protocols correct?
Are any firewalls messing with your connection?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply